SQL Server – Automating Backup Files Cleanup Task Using Maintenance Plans

« SQL Server – Get all Login Accounts Using T-SQL Query – SQL Logins, Windows Logins, Windows Groups

SQL Server – How to Move Database Files – Data File and Log File – From One Drive Location to the Other »

SQL Server – Automating Backup Files Cleanup Task Using Maintenance Plans

August 7, 2013 by Suresh Raavi

Cleaning/deleting the old backup files is equally important as taking the backups. Doing so we can (i) Avoid overhead cost for the storage space, and (ii) Ensure and retain enough space on the disk for the next successful backup

Using ‘Maintenance Plans’ this task will be a cake walk for the DBAs who are having hard time deleting the old backup files manually!

Below is the step by step process with screenshots showing how to setup and Automate backup files cleanup task using Maintenance Plans in SQL Server.

Step 1: Connect to the SQL Server instance, expand ‘Management’ folder, right click on ‘Maintenance Plans’ and select ‘Maintenance Plan Wizard’

Step 2: ‘Maintenance Plan Wizard’ pops out, click Next

Step 3: On the ‘Maintenance Plan Wizard’ give a Name and Description. To schedule this as a job, select Change under ‘Schedule’

Step 4: ‘New Job Schedule’ pops out. Define a schedule. In my case I gave it to run Daily at 12:00:00 AM. Click ‘ok’ when done.

Step5: Make sure everything is correct and click Next

Step 6: Select the Maintenance Cleanup Task option and click Next

Step7: Here we only have one task, so nothing to order/re-order. Click Next

Step 8: This window is the heart for this task. Under ‘Delete files of the following type’ select Backup files. Under ‘File location’ select Search folder and delete files based on an extension. Give the path of the backup files location in Folder and “bak” (for native backups) in File extension. For Light Speed Backups you need to mention ‘lsb’.

Under ‘File age’ check the option Delete files based on the age of the file at task run time. Under ‘Delete files older than the following’ select a retention policy. I left the default 4 weeks. Click Next

Step 9: The wizard will write a report to a text file to the defined Folder location. You can leave the default location or chose a location of your choice. Click Next

Step 10: Click Finish

This should create the Maintenance Plan ‘Delete old backup files’ as shown in the screenshot below

Note: SQL Agent Services must be running in order to execute the above discussed Maintenance Plan.

Posted in Backup, Disk Space, SQL Server Agent | Tagged SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 | 17 Comments

17 Responses

on August 7, 2013 at 7:20 am | Reply siva

Hi Everybody,

Using maintain plan also we have to do the same activity using maintaince clean up task. What is advantage of this.
As per my understanding using there no any advantage if im was wrong please correct me.

Regards
Siva
- on August 7, 2013 at 5:34 pm | Reply Venkata Suresh Raavi
  
  Hi Siva, first of all, thanks for your comment!
  
  Above I discussed how to create a “maintenance plan” to do the “maintenance cleanup task” and how to schedule it to run as a job.
  
  The advantage is, it automatically deletes backup (.bak) files older than the retention period defined without having to manually delete them.
  
  Hope I answered to you question, if not please let me know.
  
  Regards,
  Suresh.
on August 7, 2013 at 7:23 am | Reply siva

And one more dought using this task we can delete our old transaction logs or not please suggest me
.bak file we have deleted.
.trn files also auto matically deleted.
- on August 7, 2013 at 10:29 pm | Reply Venkata Suresh Raavi
  
  No. You need to create a new maintenance cleanup task to delete the log (.trn) files which can be either part of the same maintenance plan or a different maintenance plan.
  
  Regards,
  Suresh.
on October 2, 2014 at 2:20 am | Reply droperosthia

Thanks a lot! Really helped me out here!
on October 2, 2014 at 6:34 pm | Reply Suresh Raavi

Glad, it helped!
on May 27, 2015 at 1:03 am | Reply hardik Patel

this article really useful thanks lots
on May 27, 2015 at 1:09 am | Reply hardik Patel

with maintenance plan possible to send mail with attached bake up file…. is this possible ?
if yes then how…?
can you explain me… i really need it

thanks
on June 1, 2015 at 12:11 am | Reply Gautam

You can use *.* to delete the .trn and .log using one maintenance plan
on July 8, 2015 at 11:28 am | Reply mhorkpheus

muchas gracias hoy vere si funciona
on September 8, 2015 at 12:20 am | Reply Manoj Singh

I am not able to delete old file, follow the same process, job was completed success fully but not remove the old file (1 Day Old file)
on November 25, 2015 at 8:03 am | Reply mlevasseur1

Works great on SQL 2014. Good article.
on February 1, 2016 at 2:04 pm | Reply SQL Server: Maintenance Plans – Business Ops

[…] SQL Server – Automating Backup Files Cleanup Task Using Maintenance Plans […]
on March 28, 2016 at 7:55 am | Reply vamsi krishna

HI have a question regarding maintenance plan. In my plan first step is delete old backups i dont know what happened internally while this job runs

1) These are OS level backups ?
2) How sql server job is communicating with OS to delete backup files in the particular backup folder ?

Can any one tell me how the call flow from sql server to OS level.

Because i have an issue that delete backup step is taking all most 2 days i dont know where i am stuck……………….
on June 5, 2016 at 8:07 pm | Reply Parimi

How to connect the jobs to go in the proper order
on June 5, 2016 at 8:18 pm | Reply Chowdary

How to connect the jobs in sql server
on September 19, 2016 at 9:27 am | Reply Pavan Patel

i wanted to exclude some of the folder , can we do it ? For example. I have maintenance plan for clean up task for 2 week. That means delete any files within subfolder that is more than 2 week older. The DB back maintenance will create backup in their DB folder. Let see one of the DB that i am not using now and making it offline and backup process is not taking backup of that DB because of offline. after two weeks pass that DB backup getting old and based clean up task configuration, it delete this Back up files because it older than 2 week. Ho can we exclude of the folder from deletion ?