Setting Up a New SQL Maintenance Plan

1. Open SQL Server Management Studio
   
2. Connect to the database using [ . ] or server name
   

Setting up security

1. Expand Security in the left hand window
2. Expand Logins in the left hand window
3. Double-click on BUILTIN\Users
4. Click on server roles down the left-hand side window and Clear SETUPADMIN & SYSADMIN
5. Click OK

6. Click on Management (in the left-hand side list) and expand
7. Right-click on Maintenance Plans and select Maintenance Plan Wizard
8. Click Next on the first screen

9. Select on Separate schedule for each task

10. Click next
11. Select Back Up Database (Full), Back Up Database (Transaction Log) and Maintenance Cleanup Task then click next, then next again

A Full backup Task

1. Select the database you wish to backup (most often this is only Abel) then click OK
2. Click on destination (in newer versions of SQL this will be a tab at the top of this dialog window)

3. Select 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)
4. Click Next when finished

B Transaction Log backup task

1. Select the Database you want to backup (Most often this is only Abel) then click OK
2. Click on Destination (in newer version of SQL this will be a tab at the top of the window

3. Select Create a sub-directory for each database and add the path where you this backup to save to (most often it will be C:\ABELDent\Data\Backup but varies depending on the office
4. Click next when finished

C Define Maintenance Cleanup Task (.bak)

1. Select Include First Level Subfolders and Select Cleanup Job Path (most often it will be C:\ABELDent\Data\Backup but varies depending on the office)
2. Note: File extension on this cleanup job should be BAK
3. Change Delete Files Older than the following to: 2 Week(s)

4. Click Next
5. Click Next again
6. Click Finish

Editing times for scheduled jobs / adding second cleanup for TRN files

1. Right-click and refresh on maintenance plans

2. Double-click to modify newly created Maintenance Plan (The freshly created maintenance plan should open)

3. Select subplan_3 (maintenance Cleanup task)
4. Select toolbox (top left corner of the window) to expand and Double-click on maintenance plan cleanup task to add to subplan3

5. Note: your screen should show:
   
6. Click on the green arrow of this plan and Drag and Drop it over top of the Maintenance Cleanup Task

Define Maintenance Cleanup Task (TRN)

1. Double click on Maintenance Cleanup task 1
2. Select Include First Level Subfolders and Select Clanup Job Path (most often it will be C:\ABELdent\Data\Backup but varies depending on the office)
3. Change Delete files older than the following to: 2 Week(s)
4. Note: File extension on this cleanup job should be TRN

5. Click OK

Setting Schedules for Plans to run Automatically

1. Click on icon as shown below for subplan_1

2. Set the job schedule to run (occurs) Daily and set the Frequency to Once at 6 am

3. Click OK
4. Click on icon for Subplan_2
5. Set the job schedule to run (occurs) daily and set the Frequency to every 4 hours

6. Click OK
7. Click on icon for Subplan_3
8. Set the job schedule to run (occurs) Weekly and set the frequency to Weekly on Monday, Wednesday, and Friday

9. Click OK
10. Upon completion it should appear like:
    

Running the Jobs

The jobs created appear:

1. Right-click on MaintenancePlan.Subplan and select start Job at Step 1
2. Repeat for Subplan_2 and Subplan_3

Product (s): ABELDent, ABELMed, Portal
Classification: Internal and External
Date Created: August 1st, 2019
Created by: John Scarlett