In this article, we show how to create a new SQL Maintenance Plan that will backup databases on a daily and weekly basis with example. To demonstrate the SQL Maintenance Plan, 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 in SQL Server.
Create SQL Maintenance Plan
In this example, we will create a SQL Server Maintenance Plan that will perform regular database backups. For this, we use two types of backups: Full (Weekly), and Differential (Daily)
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 open to assign a new for this plan. For this SQL Maintenance Plan demo, we assigned the name as AdventureWorksBackup.
Once you click the OK button, the following window will appear, along with the Toolbox.
Double click on the Subplan_1 to rename it. As you can from the below screenshot, we assigned a name as FullBackup and valid description.
Let me add another subplan for differential backups. To create a new subplan in SQL Server, Please click on the Add Subplan tab, as we have shown below.
And, we assign the name as Differential Backup for this SQL Maintenance Plan.
Please select the Full Backup subplan, 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, the following window will open.
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 Management Studio. For now, we are choosing the Adventure Works 2014
Please cross-check all the options, and click on the Destination tab to configure the destination.
The following are 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 …. (Browse) button to choose the path.
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 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.
Let me close the SQL Server Maintenance plan and save it.
Please go to the SQL 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).
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.
Modify the SQL Maintenance Plan
Right-click on the newly created maintenance plan in SQL Server will open the context menu. Please select the Modify option.
To schedule the backup, please click on the tiny calendar symbol, as we showed 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.
We don’t have time to wait for weekdays and weekends. Let me start the Job by right-clicking on the Job and select Start Job at Step.. option from the context menu.
As you can see, our Job executed successfully.
From the file system you can see, we had one Full backup and one differential backup.
