Author Archives: admin

Create a Database Backup Cleanup Maintenance Plan

Create a Database Backup Cleanup Maintenance Plan

  1. Open SQL Server Management Studio
  2. Login to SQL Instance
  3. Click on the plus sign next to the Management folder to expand it.Image1__MainPlan_Navigation
  4. Right-Click on the Maintenance Folder and select Maintenance Plan Wizard.Image2_MainPlan_NavSelection
  5. Click NextImage3_MainPlan_WizardStart
  6. Enter Plan name and select “Separate schedules for each task”
  7. Click NextImage4_MainPlan_PlanName
  8. Select Maintenance Cleanup Task and click Next.Image5_MainPlan_TaskSelection
  9. Click Next
  10. On the Define Maintenance Cleanup task, make the following selections:
  11. Delete files of following Type: Leave Default selection “Backup files”
  12. File Location: Choose “Search folder and delete files based on an extension”
  13. Folder: Enter the path and folder where Backup Job saves backup files
  14. File Extension: bak (Do not include the “.” in the file extension text box!!)
  15. Check – Include first-level subfolders.  (First-level folders must be included to help reclaim drive space.)
  16. File age:  Check – Delete files based on the age of the file at task run time
  17. Delete files older than the following: 14 DaysImage6_MainPlan_TaskDetails
  18. Click ChangeImage7_MainPlan_TaskSchedule
  19. Click OKImage8_MainPlan_TaskScheduleComplete
  20. Click NextImage9_MainPlan_ReportOption
  21. Click NextImage10_MainPlan_TaskFinish
  22. Click FinishImage11_MainPlan_WizardCloseJPG
  23. Click Close
  24. Click on the plus sign to the left of Maintenance PlansImage12_MainPlan_NavSelection2
  25. Right-Click on the maintenance plan and choose ModifyImage13_MainPlan_MenuSelection2
  26. Choose Add SubplanImage14_MainPlan_MenuPoint
  27. Name the Subplan and click on the Schedule buttonImage15_MainPlan_TLogCreateSubPlan
  28. Configure the schedule as below.Image16_MainPlan_TLogSchedule
  29. Click OK
  30. Click OKImage17_MainPlan_TLogConfirm
  31. Click and drag the Maintenance Cleanup Task to the Designer SurfaceImage18_MainPlan_TLogDragObjectImage19_MainPlan_TLogObjectThere
  32. Double-Click on the Maintenance Cleanup Task
  33. On the Define Maintenance Cleanup task, make the following selections.
  34. Delete files of following Type: Leave Default selection “Backup files”
  35. File Location: Choose “Search folder and delete files based on an extension”
  36. Folder: Enter the path and folder where Backup Job saves backup files
  37. File Extension: trn (Do not include the “.” in the file extension text box!!)
  38. Check – Include first-level subfolders.  (First-level folders must be included to help reclaim drive space.)
  39. File age: Check – Delete files based on the age of the file at task run time
  40. Delete files older than the following: 3 DaysImage20_MainPlan_TLogConfigObject
  41. Click OK
  42. Click the Save ButtonImage21_MainPlan_TLogSave
  43. Click Add SubplanImage22_MainPlan_DiffCreateSub
  44. Name the Subplan and click on ScheduleImage23_MainPlan_DiffNameSubPlan
  45. Configure the schedule as below.Image24_MainPlan_DiffSchedule
  46. Click OK
  47. Click OK
  48. Click and drag the Maintenance Cleanup Task to the Designer SurfaceImage25_MainPlan_DiffDragObjectImage26_MainPlan_DiffObjectThere
  49. Double-Click on the Maintenance Cleanup Task
  50. On the Define Maintenance Cleanup task, make the following selections.
  51. Delete files of following Type: Leave Default selection “Backup files”
  52. File Location: Choose “Search folder and delete files based on an extension”
  53. Folder: Enter the path and folder where Backup Job saves backup files
  54. File Extension: bak (Do not include the “.” in the file extension text box!!)
  55. Check – Include first-level subfolders.  (First-level folders must be included to help reclaim drive space.)
  56. File age: Check – Delete files based on the age of the file at task run time
  57. Delete files older than the following: 7 Days
  58. Click OK
  59. Click the Save ButtonImage27_MainPlan_DiffSaveButton
  60. Double-Click on Subplan_1 to open the Subplan properties WindowImage28_MainPlan_RenameSubPlan
  61. Change the Name and Description to “Full Cleanup”Image29_MainPlan_NameSubPlan
  62. Click OK
  63. Click the Save ButtonImage30_MainPlan_RenameSave
  64. Click the “x” in the upper right hand corner to close the Maintenance Plan

Image31_MainPlan_End

 

Written By: Montrial Harrell