Setting up a new SQL maintenance plan
Open SQL Server Management Studio
1. Connect to the database using (.) or server name
2. Expand security / logins then double click on BUILTIN\Users
Click on server roles down the left hand side (of Login Properties – BUILTIN\Users Dialog box)
and check off SETUPDAMIN and SYSADMIN
3. Click OK
October 29, 2019 1|Page
4. Click on management (in the left hand side list) and expand Right click on Maintenance plans
and select maintenance plan wizard
5. Click next on first screen
6. Click on separate schedule for each task
7. click next
8. Check off back up database (Full), back up database (Transaction Log) and Maintenance
Cleanup task then click next, then next again.
October 29, 2019 2|Page
Full Back up task
9. Check the box of the database you want to backup (most often this is only Abel) then click OK
10. Click on destination (in newer versions of SQL this will be a tab at the top of this dialog window
11. Check Create a sub-directory for each database and add the path where you want this backup
to save to (most often it will be C:\ABELDent\Data\Backup but varies depending on the office)
12. Click next when finished
Transaction Log backup task
13. Check the box of the database you want to backup (most often this is only Abel) then click OK
Now click on destination (in newer versions of SQL this will be a tab at the top of this dialog
window
October 29, 2019 3|Page
14. Check Create a sub-directory for each database and add the path where you want this backup
to save to (most often it will be C:\ABELDent\Data\Backup but varies depending on the office)
15. Click next when finished
Define Maintenance cleanup task
16. Check box for include first level subfolders and select cleanup job path (most
often it will be C:\ABELDent\Data\Backup but varies depending on the office)
17. NOTE: File extension on this cleanup job is should be BAK 18. Change Delete
files older than the following to 2 weeks
October 29, 2019 4|Page
19. Click next
20. Click next again
21. Click finish …. (you are not done !!)
Editing times for scheduled jobs / adding second cleanup for TRN files
22. Right click and refresh on maintenance plans
October 29, 2019 5|Page
23. Double click to modify newly created MaintenancePlan. (The maintenance plan you just created
should open)
24. Click on subplan _3 (maintenance Cleanup task)
25. Click toolbox (top left corner) to expand … double click on maintenance plan cleanup task to
add to subplan3
October 29, 2019 6|Page
NOTE: You should now see this
26. Click on the green arrow of this plan and drag and drop it over top of the original Maintenance
Cleanup Task
Define Maintenance cleanup task 1
27. Double click on Maintenance Cleanup task 1
October 29, 2019 7|Page
28. Check box for include first level subfolders and select cleanup job path (most often it will be
C:\ABELDent\Data\Backup but varies depending on the office)
29. Change Delete files older than the following to 2 weeks NOTE: File extension on this cleanup job
is should be TRN
30. Click OK
Setting schedules for plans to run automatically
31. Click on icon as shown here for Subplan_1
32. Set the job schedule to run (occurs) Daily and below this set occurs once at: change to 6am
October 29, 2019 8|Page
33. Click OK
34. Click on icon for Subplan_2
35. Set the job schedule to run (occurs) Daily and below this set occurs every: change from every 1
hour to every 4 hours
36. Click OK
37. Click on icon for Subplan_3
October 29, 2019 9|Page
38. Set the job schedule to run (occurs) Weekly and below this set Recurs every: click Monday,
Wednesday and Friday
39. Click OK
When you are done it should look something like this
Running the jobs These are the jobs you’ve created
40. Right click on MaintenancePlan.Subplan_1 and select start Job at Step
41. Do the same on the 2 others you’ve created 42. That’s all there is.
October 29, 2019 10 | P a g e
