SQL Maintenance AutoDelete older backups

SQL server has flexibility for automatic database backup in the easiest way, but after few days hard drive size gets overflow due to multiple iteration of database backup. This is the additional task for IT admin to delete older instances of database backup.

With creating SQL Clean up maintenance plan in such cases to delete automatically the older database backups.

With considering above objective, kindly go through below steps to delete old database backup files automatically in SQL Server using SQL Server Maintenance plan:

1. When connected to the server, expand it and the Management folder after. Then right click on Maintenance Plans and click on Maintenance Plan Wizard

2. In the Maintenance Plan Wizard, click Next

3. Enter a Name, Description, and in the bottom right click on Change

4. When a New Job Schedule window pops up, check if the Schedule type is set to Recurring. After that, we    can set up the schedule using the options below. After that, click OK.

5. Check if everything is correct before clicking on Next:

6. In the next window, check Maintenance Cleanup Task and click on Next:

7. In the following window, because we have only one task, there is no ordering, so we proceed by clicking on Next:

8. In the following window:

• Under Delete files of the following type: we select Backup files by clicking on the radio button.
• Under File location: we select Search folder and delete files based on an extension. 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.

 
Note: The file extension we input must not contain dot (‘.’) – ‘.bak’ is incorrect, ‘bak’ is correct

• Under File age: we check the option Delete files based on the age of the file at task run time and specify the age of the files below

After checking that everything is correct, we proceed by clicking Next:

9. In the next window, we may select that a report is written and/or mailed to the email address we input every time the Maintenance plan runs.

10. In the next window we press Finish to complete the creation of our Maintenance Plan. After that we can check under Management → Maintenance Plans for our newly created plan:

Based on time which configured in maintenance plan, automatically older database backup will get deleted. 

We Urge You To Call Us For Any Doubts & Clarifications That You May Have. We Are Eager to Talk To You

Call Us: +91 7406663589