SQL Maintenance Plan

In this article we will show you, How to create new SQL Maintenance Plan that will backup database on daily, and weekly basis with example. To demonstrate the same, we are using the Adventure Works Database present in our SQL Server.

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 that will perform regular database backups. For this we will use two types of backups: Full (Weekly), and Differential (Daily)

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

Once you select the New Maintenance Plans… option, a new window will be opened to assign a new for this plan. For now, we assigned the name as AdventureWorksBackup.

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

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

Let me add another sub plan for differential backups. In order to new subplan, Please click on the Add Subplan tab as we shown below.

and we assigns the name as Differential Backup

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

Right click on the Task will open the context menu, please select the Edit.. option to configure the Task.

once you select the Edit.. option, following window will be opened.

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

Database: Here you can select either one, or all the database exists in SQL Server management Studio. For now, we are selecting the Adventure Works 2014

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

Following are the list of properties available in the 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 …. (Browse) button to select the path.

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

You have to follow the same steps to create a Differential Backup, and the only change is Backup Type to Differential as we shown below.

Let me close the 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 plan. Next, Expand the SQL Server Agent -> Jobs folder to check the newly created Jobs (Full, and Differential).

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

You can schedule them by clicking the New button but we will do it in the Maintenance plan section. This will help you to understand the topic completely.

Modify SQL Maintenance Plan

Right click on the newly created maintenance plan will open the context menu. Please select the Modify option as we shown below.

In order to schedule the backup, Please click on the tiny calendar symbol as we shown here.

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

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

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

As we don’t have time to wait for weekdays, and weekends. Let me start the Job by right-click on the Job and select Start Job at Step.. option from the context menu.