Tutorial Gateway

  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
  • MySQL

SQL Maintenance Plan

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.

SQL Maintenance Plan 1

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.

SQL Maintenance Plan 2

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.

SQL Maintenance Plan 3

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

SQL Maintenance Plan 4

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.

SQL Maintenance Plan 5

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.

SQL Maintenance Plan 6

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

SQL Maintenance Plan 7

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

SQL Maintenance Plan 8

Right-click on the Task will open the context menu. Please select the Edit.. option to configure the Task.

SQL Maintenance Plan 9

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

SQL Maintenance Plan 10

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

SQL Maintenance Plan 11

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

SQL Maintenance Plan 12

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

SQL Maintenance Plan 13

The 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 choose the path.

SQL Maintenance Plan 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 the Verify backup integrity and click OK button.

SQL Maintenance Plan 15

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

SQL Maintenance Plan 16

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

SQL Maintenance Plan 17

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.

SQL Maintenance Plan 18

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.

SQL Maintenance Plan 19

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

SQL Maintenance Plan 20

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

SQL Maintenance Plan 21

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

SQL Maintenance Plan 22

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

SQL Maintenance Plan 23

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.

SQL Maintenance Plan 24

As you can see, our Job executed successfully.

SQL Maintenance Plan 25

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

SQL Maintenance Plan 26

Filed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW

Copyright © 2021· All Rights Reserved by Suresh.
About | Contact | Privacy Policy