THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Backup and the evil RETAINDAYS option

"So what bad has this option done?", you probably as yourself. Well, not much, but I find it evil because it confuses people, especially those new to SQL Server. I have many times seen people specifying something like 3, and expect SQL Server to keep the three most recent backups in the backup file and overwrite everything which is older than that. Well, that is not what the option does.

But before we go into details, let's look at an example backup command which is using this option:

BACKUP DATABASE sqlmaint TO DISK = 'R:\sqlmaint.bak' WITH RETAINDAYS = 3

The RETAINDAYS is also exposed in the backup dialog in SSMS: "Backup set will expire: After x days".

It is also exposed in Maintenance Plans, the backup task. The option is named "Backup set will expire: After x days". It is only enabled if you select the "Back up databases across one or more files" option, which is not the default option. This makes sense.
The default option is "Create a backup files for every database", which means that every time a backup is performed, a new file is created consisting of the database name, date and time. Since we will see that this option is only relevant when we do append, it makes sense in the RETAINDAYS not being enabled for this choice.

So what does this option do? All it does is make SQL Server return an error message of you try to do a backup using the INIT option (which means overwrite) before the date and time has occurred. In other words, it tries to help you in not overwriting a backup file, using the INIT option, before it is time. You can still overwrite earlier by either using the stronger FORMAT option instead of INIT; or by simply deleting the backup file. Btw, the RETAINDAYS parameter has a cousin named EXPIREDATE, which does the same thing but you specify a datetime value instead of number of days.

Backup generations
So, we have seen that RETAINDAYS do not in any way provide any automatic backup generation handling. There is no such functionality built-in in the BACKUP command. This means that when you are looking for functionality such as "keep backup files three days, and remove older than that", you need to look outside the BACKUP command. I.e., some script or tool of some sort.

I have an example (without the "delete old files" part) here, which I mostly created as a starting point for those who want to roll their own and want to have some example to start with. Many of you are probably using Maintenance plans (the "Create a backup files for every database" option in the backup task, along with Maintenance Cleanup task). Another popular script/tool for this is Ola Hallengren's Maintenance Solution, which you find at http://ola.hallengren.com/.

Published Sunday, July 08, 2012 3:25 PM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jeff Bennett said:

Thanks, Tibor.

That has always been confusing.  Appreciate the insight.

July 9, 2012 2:12 PM
 

Ian Yates said:

We're an ISV who uses SQL Server and I had noticed some of our client's IT staff had set this option in the maintenance plans and then complained that SQL wasn't cleaning up the old backups.  Rather than have a debate with them about it I've just started using Ola's scripts - they're great :)

July 10, 2012 12:22 AM
 

Roger said:

Clean, useful and experience-based explanation, thanks.

December 18, 2012 4:45 AM
 

sanjay dobriyal said:

thanks Tibor, very clear and nice article.

February 12, 2013 2:26 AM
 

Shivram said:

Thanks Tibor...yes, this option has been confusing. thanks for the insight.

September 17, 2013 7:44 AM
 

kishor said:

Hi Tibor..

Please Look into this ... i created a backup with expiration date.. after that date i used the same expired backup to restore a db and it worked.. SO what is the use of EXPIRATION here

December 19, 2013 2:33 PM
 

TiborKaraszi said:

The expiredate option *do not* make a backup non-restoreable after the date. The only thing it does is to prohibit overwriting it using INIT before the date occurs. As I stated, pretty useless...

December 19, 2013 2:38 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement