Backup.ExpirationDate in Sql Server SMO

I'm writing a console app in Visual Studio 2005 to backup databases using SMO and I have run into a minor snag.

It appears that when I have the Initialize property set to false to allow backups to append onto existing backups, the backups that are past their expiration dates do not get overwritten. I know the RetainDays property requires the Intialize property to be set to true but I haven't seen anything that suggests that the ExpirationDate property does as well.

Does anyone have any suggestions?

I don't think you have run into a minor snag, you have run into desired and expected behavior because Backup is the best thing you have to recreate a SQL Server database including the an actual copy of the database. The reason is when backup is copied to tape, then the .bak file can be deleted manually. That said I think you can find some backup code in the BOL (books online) but try the link below to get started. Hope this helps.

http://www.sqldbatips.com/showarticle.asp?ID=37

|||

Thanks for the response Caddre.

I have the backup code written and the application works fine. I'm just afraid that if the backups go unmonitored (and in our case that is a possibility) the backup file will get extremely large.

Every time I run the console app it appends to the end of an existing backup .bak file, thus doubling it in size. I thought if you established an expiration date, "expired" backups would be overwriiten within the .bak file. That way the .bak file would never be more than twice its size; however, my testing shows that it just keeps getting bigger and bigger.

Thanks again

|||That is what I am trying to explain to you Backup files cannot be allowed to be overwritten because someone could make the mistake of overwriting needed .bak files. You could write code to delete expired .bak files or you could do it manually weekly.