Appending backups while overwriting expired sets I've been reading up on this, but the online documentation is a bit difficult to understand and seems awkwardly written.  For example the INIT, 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?

Edit:

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
Unfortunately, no, that won't work.  It will keep appending but will leave the expired sets in the same archive.  This screenshot shows a backup archive using your script, set to RETAINDAYS = 2, run once in November (VM trickery) and again today.



I think what you're after is a rolling backup, so assuming this is weekly, then after a burn in period, it should retain 7 old backup sets, remove one and append a new one.

Personally, I would keep a single backup set per file and use the cleanup feature in the maintenance wizard to remove old files; that or a regular Windows Scheduled Task.  There shouldn't be any real need to keep backup sets in a single file.