SQL Maintenance Plan

In this article, we show how to create a new SQL Maintenance Plan that will back up databases daily and weekly with an example. To demonstrate the SQL Server Maintenance Plan, we are using the Adventure Works Database present in our system.

Before we get into the example, let me show you the existing maintenance plans in SQL Server. To access the same, go to the Object Explorer -> Expand the Management Folder, and expand the Maintenance Plans. As you can see from the below screenshot, we don’t have any existing maintenance plans.

Create SQL Maintenance Plan

In this example, we will create a SQL Server Maintenance Plan to perform regular database backups. For this, we use two types of backups: Full (Weekly), and Differential (Daily)

To create, Please right-click on the SQL Maintenance Plans and select the New Maintenance Plans… option from the context menu.

Choose New SQL Maintenance Plan option to create one 2

Once you select the New option, a new window will open to assign a new one for this SQL Maintenance Plan. For this demo, we assigned the name as AdventureWorksBackup.

Assign New name 3

Once you click the OK button, the following window, along with the Toolbox, will appear.

SQL Maintenance Plan Back up database Taskoption 4

SQL Maintenance Plan Subplan

Double click on Subplan_1 to rename it. As you can see from the below screenshot, we assigned a name as FullBackup, and an accurate description.

Set subplan Name, description, schedule, and run as option 5

Let me add another subplan for differential backups. To create a new subplan in SQL Server, click on the Add Sub plan tab, as shown below.

Add Subplan to SQL Server Maintenance Plan 6

And, we assign the name Differential Backup for this SQL Maintenance Plan.

Subplan Properties 7

Please select the Full Backup subplan, and drag and drop the Back Up Database Task on the designer region.

Schedule SQL Maintenance Plan 8

Edit Subplan

Right-click on the Task will open the context menu. Please select the Edit.. option to configure the Task or edit SQL Maintenance Plan subplan.

Edit SQL Maintenance Plan 9

Once you select the Edit.. option, the following window will open.

Backup Database Task General Settings 10

Backup Type: Please select the backup type from the drop-down list.

Backup Type 11

Database: Here, you can select one or all of the database that exists in Management Studio. For now, we are choosing Adventure Works 2014.

Local Server Connection and Choose the Database 12

Please cross-check all the options, and click on the Destination tab to configure the destination.

backup database 13

The following is the list of properties available in the SQL Maintenance Plan Destination tab. As you can see, we are selecting the D folder to save the backup files, and its extension is bak. You can use the…. (Browse) button to choose the path.

Select Folder to backup database 14

Within the Options tab, you can set the expiry, Encryption, etc. For now, we don’t want any compression or Encryption. So let me select Verify backup integrity and click the OK button.

Back Up Database Task Conection 15

You must follow the same steps to create a Differential Backup; the only change is from Backup Type to Differential.

Back Up Database Task 16

Let me close the SQL Server Maintenance plan and save it.

Please go to the Object Explorer -> Expand the Management Folder and expand the Maintenance Plans to see the newly created one. Next, Expand the SQL Server Agent -> Jobs folder to check the freshly created Jobs (Full and Differential).

Backup Database Job under the Object Explorer

Select and right-click on either of the two Jobs and select the properties option from the menu. Next, under the Schedules tab, you can see there is no scheduled time for this Job to run. It is because we missed scheduling the backups.

You can schedule them by clicking the New button, but we will do it in the SQL Maintenance Plan section. It helps you to understand the topic altogether.

Job Schedule Properties Window

Modify Maintenance Plan in SQL Server

To modify it, Right-click on the newly created maintenance plan will open the context menu. Please select the Modify option.

Modify SQL Maintenance Plan 19

To schedule the backup, please click on the tiny calendar symbol, as we showed here.

Schedule Job 20

As you can see, we are scheduling this Job to run Every Monday at 12 AM

New Job Schedule Frequency 21

Next, we are scheduling the Differential Backup Job to run Every Day at 3:31:59 PM

SQL Maintenance Plan New Job Schedule 22

Now, If you open the schedules tab in Job Properties, you can see the schedule list.

Difference BackUp Job Properties 23

We don’t have time to wait for weekdays and weekends. So let me start the Job by right-clicking on the Job and selecting Start Job at Step.. option from the context menu.

Start Jobs under the Agent 24

As you can see, our Job executes successfully.

Executing Job 25

From the file system, you can see that we had one Full backup and one differential backup.

Differential Backup Files 26
Categories SQL