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?

TO  DISK = N'c:\Backups\mydb.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'mydb-Full Database Backup', NOSKIP, 

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', 
  • Have you tried testing it for yourself on a non-important backup set?
    – user507
    Dec 6, 2012 at 15:28
  • Well I test ran it, but I'll have to wait two months to know if it will do what I need it to. I could change the retaindays to 1 and test it again tomorrow, but that's slower and less reliable than asking db admin experts.
    – MrVimes
    Dec 6, 2012 at 15:29
  • 1
    @MrVimes :-) "Reliable" = testing it out and having proof. Dec 6, 2012 at 15:30
  • So rather than someone answering my question I have to alter the query to retain one day, wait two days, and see if today's backup has gone?
    – MrVimes
    Dec 6, 2012 at 15:32
  • @MrVimes Not saying that at all. By all means get input from us here, but I'd recommend testing out and seeing with your own eyes whatever answer you get. A fact is something you can prove. Just my $0.02. Dec 6, 2012 at 15:34

1 Answer 1


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.

enter image description here

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.

  • Related: dba.stackexchange.com/questions/29455/…
    – 孔夫子
    Dec 7, 2012 at 0:40
  • Thankyou. So would I have my backup script create a new filename each time (maybe with the date appended on the filename)?
    – MrVimes
    Dec 7, 2012 at 15:30
  • @MrVimes Sounds like a perfect idea
    – 孔夫子
    Dec 7, 2012 at 15:51
  • FYI I've updated my question with a script to create a file each time.
    – MrVimes
    Dec 7, 2012 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.