Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Have you tried testing it for yourself on a non-important backup set? –  Shawn Melton Dec 6 '12 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 '12 at 15:29
1  
@MrVimes :-) "Reliable" = testing it out and having proof. –  Thomas Stringer Dec 6 '12 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 '12 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. –  Thomas Stringer Dec 6 '12 at 15:34

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
Related: dba.stackexchange.com/questions/29455/… –  孔夫子 Dec 7 '12 at 0:40
    
+1 for a single backup per file. Appending is fine until a corruption occurs, then everything's gone –  Paul White Dec 7 '12 at 0:48
    
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 '12 at 15:30
    
@MrVimes Sounds like a perfect idea –  孔夫子 Dec 7 '12 at 15:51
    
FYI I've updated my question with a script to create a file each time. –  MrVimes Dec 7 '12 at 17:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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