There are times when maintenance plan is too simple to do the job, and SSIS is unreachable for you. Here is a simple stored procedure that will make use of xp_cmdshell stored procedure to cleanup backup files from a folder. You can add any additional logic relevant to your setup.
- Disclaimer: You know database backups are like life support for DBA in times of crisis. Mess up with it at your own risk. Test well and use it with caution.
- Note: Enabling xp_cmdshell makes your server open to threats. Please see BOL on this topic. Disable it immediately after use.
Enough warnings!! Now lets see what the procedure does. The procedure assumes that the backup filename starts with the database name eg. Mydb_someformat.bak. The backup date is calculated connecting to the msdb database. This procedure will work only on the corresponding server.
The procedure uses two parameters.A) The folder to check and b) Number of days retention ie, If you need to cleanup files older than 10 days, your second parameter is 10.
The advantage of the procedure is that you can add any custom logic to the procedure to suit your requirement.
A table is created to keep track of all the deletes.