How to delete old database backup files automatically in SQL Server

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.
maintenance cleanup task not deleting files

SQL Server 2008 R2 Maintenance Cleanup Task Not Deleting bak Files

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.

Related posts:

Database development

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. Double-click the Maintenance Plan defined in the previous procedure. The maintenance plan will open in the display pane on the right side portion of the window.

VIDEO: MSSQL Maintenance cleanup task does not delete older files

Whether you use a Maintenance Cleanup Task as part of a SQL or looked for the file extension and deleted any matches older than the date. of my TLog files, and therefore could not verify that they are backup files. Hi, I’m having problems getting the Maintenance Cleanup Task to delete files. I’m on a 2 node cluster and my maintenance plan is putting my. In the cleanup step I set it to delete backups older than 7 day. was working correctly, so as you guessed, they were not being deleted. text files I dkey.finnflare.ru so for backup files I should dkey.finnflare.ru: WRONG! A check of the logs shows the clean up task ran without error, yet is still not deleting the old files.

Leave a Reply

Your email address will not be published. Required fields are marked *