Just the other day one of my customers asked me that they are facing a situation where they have specified in their backup script RETAINDAYS option but it is actually not deleting their older database backups.
I get this question quite a lot of time. I believe the word RETAINDAYS gives impressions to user that when the SQL Server backup is created they remains available till the days the RETAINDAYS option suggests and after that it automatically gets deleted.
Well, here is the truth – the assumption is absolutely WRONG.
If you want to delete your backup files which were created earlier, you may want to go with the route of sqlcmd or powershell.
The option RETAINDAYS achieves a very different purpose with Backup. It just prevents users to overwrite the backup file if the user is trying to do it with INIT option. Otherwise, it really does not do anything else. If the user is using the FORMAT option, the backup will be overwritten anyway. This means the use of RETAINDAYS is very much limited.
I guess that’s it. There is nothing much we can write about this option as it has very limited capabilities. I hope now onwards I will be able to point users to this blog post when they reach out to me with the question about this option.
Here is the working script of how this option is used in SQL Server.
BACKUP DATABASE AdventureWorks TO DISK = 'd:\adv.bak' WITH RETAINDAYS = 7
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
Hallo Dave,
I remember that there was an option for limit number of last database backups in SQL 2008 (R2) but I cannot find it in the SQL 2016 any more.
Similarly there was some “graphical” interface to define maintenance jobs and it has also gone…
If you could point all those mislead people to how properly make backups get deleted after X days… w/o that info this post is pointless.