Delete xp backup files

Automated backup and restore strategies ensure the recovery plan is successful. However, strategies relying on frequent backups, although part of a sound strategy, can start causing issues when available storage space becomes limited. Old database backups are in many cases unnecessary, and it is often prudent to delete those backups to increase available storage.

This can be done manually or automatically. There are several options to delete old backup files automatically in SQL Server:. It requires basic knowledge and understanding of Transact-SQL T-SQL scripting, but the process is then entirely automated, so there is no further input from the user needed. It is necessary to create a stored procedure first upon which the SQL Server Agent job we make would call to execution.

The advantage of this is the ability to use the same stored procedure across different jobs with different input parameters. Create a user stored procedure which will use the input from the SQL Server Agent scheduled job to delete old backup files. Right click on the database upon which we want to act and select New Query :. Anne Copeland 11 years ago. Doug 11 years ago. Jim Walpole 11 years ago.

AnotherStranger 12 years ago. B N 12 years ago. Theo Ramlall 13 years ago. Hamid 13 years ago. Ahmed 13 years ago. Namous 13 years ago. I don't have files older than 10 days. But the other job just doesn't seen to do the clean-up like it is supposed to. Supposed to be 3 days. I have files in there that are months old. It's not permissions as the same account runs both jobs.

And as mentioned. Attachments: Up to 10 attachments including images can be used with a maximum of 3. Addendum to the above: I see a folder that should have three days' worth of backups in it, but has five.

Several subfolders actually. Conclusion: The task is configured properly. ToddChittenden could you double check the permission again. I have a hunch it could be related to that. I know you have checked it but may be 2nd time could give you some more clue. Shashank-Singh Thanks for your quick response.

I do not see that account listed anywhere on any folder or subfolder on the Backup drive. How might permissions be the issue? When I configured the task, I picked off several databases in the list for the Backup task itself, then checked the box for "Create a sub-directory for each database" so the subfolder that got created, and the files therein, were created by the same account that runs the next step for deleting those file.

Make sure you are running the current patch level. There was a bug where SQL Server did not delete backup files. The delete function only deletes real backup files. Something I've found on and I appreciate you are using , I don't know if it's the same is that if you use a maintenance plan that does the backup and then deletes old files and you don't use the verify backup option the delete doesn't work.

Please note this is something I found through testing. It is not something that I've seen documentated so I can't confirm whether it will work for you or if it could cause other problems. It worked for me but I don't know why.

You must be logged in to reply to this topic. Login to reply. March 25, at am Hi All, My issue is the following: O. I tried with "[master]. March 25, at pm Another possible route is to make a CmdExec job step that runs a del cmd. March 29, at am Thanks for Your answers, I'll do some more test. Sometimes it likes a dot with extension '.