How to delete old database backup files automatically in SQL Server Introduction While working with a number of databases, creating a recovery plan can be challenging, especially if we want a foolproof design. Automated backup and restore strategies ensures 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.
Free Tutorials Submit dot com
How to delete old database backup files automatically in SQL Server Introduction While working with a number of databases, creating a recovery plan can be challenging, especially if we want a foolproof design.
Automated backup and restore strategies ensures 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 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. SQL Server Agent service must be running.
If the service is already running, you can skip this step: 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: In the new query window enter the following T-SQL: It reads the file header to check what type of file it is and will only delete certain types based on the input parameters we choose.
After running the query, we should end up with a stored procedure: After creating the stored procedure, we need to create a scheduled job with SQL Server Agent which will use the stored procedure with our parameters to delete the old backup files. On Steps tab, go to New…: In the New Job Step window, under General tab, enter a descriptive job name, under Database select the database which we want the job to work on and under Command: Under Advanced tab, choose what will be done after this step is done successfully.
Here we can also set how many times the step will retry as well as time interval between tries in minutes. Next tab is Schedules. Under this tab we can set up when will this job work.
Go to New…: In the new window, enter a descriptive name for the schedule. Also, for Schedule Type check that it is set to Recurring for proper scheduled operation. After that, we can set up the schedule using the options below: The rest of the tabs is optional in this use case, though, we could use Notifications tab to set up email notifications when the job is completed. After completing the previous step, our Job is created. It will run according to schedule, deleting old database backup files as set in the job itself.
If we want to check the job history, we can right click on the job and select View History: In the new window we can see the history for the selected job which executed successfully: SQL Server Maintenance plans are another way of deleting old backup files by using the cleanup task. When connected to the server, expand it and the Management folder after. After that, we can set up the schedule using the options below.
After that, click OK. Check if everything is correct before clicking on Next: In the next window, check Maintenance Cleanup Task and click on Next: In the following window, because we have only one task, there is no ordering, so we proceed by clicking on Next: In the following window: Under Delete files of the following type: Under File location: Under that, we specify where to search for the folders and what type of an extension to look for.
Also, we can check the Include first-level subfolders option if the backups are stored in separate subfolders. In the next window we press Finish to complete the creation of our Maintenance Plan. Deleting old backup files automatically is done with ease in a few simple steps with no need for using T-SQL or other scripts. To do this, we must perform the following steps: Go to Home Tab and click on Backup: When the Backup Wizard window opens, click the Advanced tab of Backup Wizard window, where we see various options for fine tuning of the backup schedule.
At the bottom of the window, under Cleanup, check the option Delete backup files older than and specify the age in days where older backup files than that age will be deleted. That way, whenever that schedule is run, it will check for old database backup files and automatically delete them: Downloads Please download the script s associated with this article on our GitHub repository Please contact us for any problems or questions with the scripts.
Chew the Fat
Hi folks, I have created some cleanup tasks within maintenance plans to clear old backup and TRN files and nothing seems to be done where are the logs for. If you break permission inheritance on your SQL backup folder, you could render your Maintenance Cleanup Task inoperable. Check the file to see if it is a valid backup file. If the file is corrupt or in some other way damaged the process to remove the file won’t do anything. You can check.
How to delete old database backup files automatically in SQL Server
At this point you have created a maintenance plan to create daily full backups, incremental transaction log backups every 2 hours, and to remove full backup files that are more than 2 weeks old. In addition to this, it is imperative that you modify the maintenance plan to remove incremental transaction log backups that are more than 2 weeks old. Failure to modify the maintenance plan to remove incremental transaction log backups will result in a situation where over time, your incremental transaction log backups will consume all available storage space. Expand the Management branch, then expand the Maintenance Plans Branch. The database maintenance plan defined in the previous procedure will be listed.
By David K. Sutton If you break permission inheritance on your SQL backup folder, and if you have a maintenance plan that uses subfolders to backup your databases by name, you could render your Maintenance Cleanup Task inoperable. Old database backup files still exist.
VIDEO REVIEW: SQL Maintenance Plan Doesn’t Delete Backups – Bradley Schacht
Step 6: Select the Maintenance Cleanup Task option and click Next This should create the Maintenance Plan ‘Delete old backup files’ as. The Maintenance Cleanup task removes files related to and seems from above that a non sql file may not be deleted via Maint’ce plans. If you are running Microsoft SQL Server R2 and find that your maintenance cleanup task is not deleting old files then it’s likely to be one of.