My goal is simple. I want to have SQL Server keep a nightly backup of a DB for a rolling 5 days.
So each night at some time I want a new DB backup made and one deleted such that I have a rolling 5 days on disk.
I am trying to get a maintenance plan set up to handle this work and have the saving of the file done (I think). I have the backups being appended so a single bak file will contain the rolling 5 days (not sure this will work 0 cause how will SQL know what to delete, and deleting the single bak file will remove all the backups).
As you can tell why am having problems figuring out how to remove older backups fo the DB so that Y have only the most recent 5 on disk.
Any tips for me on this?
Best Answer
Maintenance plans as suggested are a good way forward. One alternative is to use a scheduled job that calls a stored procedure like this one;
Adapt it to append a timestamp to the backup file name. And add any expiredate or retaindays parameters you require. Like BOBS suggested, you can also use a maintenance clean up task to clear out older backup files than 5 days. This gives you individual backups for each day. You can run it hourly or x number of times during the day or once a day, whatever you need.