Automatic Maintenance Plan to purge /clean up old backup in MS SQL server Database
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.
Reference : http://sqlserverzest.com/2013/08/07/sql-server-automating-backup-files-cleanup-task-using-maintenance-plans/
No comments:
Post a Comment