I've been reading up on this, but the online documentation is a bit difficult to understand and seems awkwardly written. For example the
NOINIT. One says it will try to overwrite everything. The other says it will append regardless.
I want something in-between that. I want expired media sets to no longer be in the file, but for new backup jobs to append to the file along side any sets that have not yet expired.
Will the SQL below perform the desired backup?
BACKUP DATABASE [mydb] TO DISK = N'c:\Backups\mydb.bak' WITH NOFORMAT, NOINIT, NAME = N'mydb-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10, RETAINDAYS = 60 GO
If I run the above, and there is a backup set older than 60 days, will it be removed from the file?
In response to RichardTheKiwi's suggestion I have rewritten the code to create a new file each time. I will then write a windows script to delete files older than a certain date
declare @filename varchar(max) set @filename = 'c:\sqlbackups\pptd_' + replace(replace(convert(varchar, getdate(), 120),':','_'),' ','_') + '.bak' BACKUP DATABASE [Peter's pointless test database] TO DISK = @filename WITH FORMAT, INIT, NAME = N'pptd-Full Database Backup', NOREWIND, NOUNLOAD, STATS = 10 GO