How To Create Full Backup Using SQL Server Maintenance Plan

dbtut January 5, 2019 MSSQL

We perform maintenance operations in SQL Server using Maintenance Plan. In this article we will create a maintenance plan for full backup. I will share article links of other processes that can be performed using SQL Server Maintenance Plans at the end of the article.

From the Management tab under SSMS, we select Maintenance Plans and Maintenance Plan Wizard as shown below.

On the next screen, select “Do not show this starting page again” as follows and click Next.

We give a name to the Maintenance Plan from the Name section as follows on the incoming screen. I named FullBackup.

Then click Change in the Schedule section. A screen appears as follows.

From Occurs you can choose Daily, Weekly or Monthly.

Select Daily if you want this maintenance plan to run daily
Select Weekly if you want this maintenance plan to run weekly
Select Monthly if you want this maintenance plan to run monthly

We schedule the job like below in the Schedule section.

On the next screen, we select “Backup Up Database(Full)” as follows and click next.

When we click “Select one or more” in the Databases section, the window appears just below.

If we select the All databases option, it will create full backup of all databases.

If we select System databases, it will create full backup of system databases.

If we select All user databases, it will create full backup of all user databases.

We can select specific databases by selecting these databases. For example, in this example we will only create Full Backup of the Test database as follows.

If you select “Ignore databases is not online” option and a database is inaccessible during the backup process, it continues for the next database.

In the next screen, if you select “Write a report to a text file” as below, it will create a report in the folder you selected in Folder location.

If you select the E-mail report option, it will send the reports to the e-mail address you specified in the To: section.

Since I have no operator defined on my server, I receive an error like the following.

There are no operators defined on the system.

You may want to read my article “How To Create An Operator On SQL Server” to get detailed information about the operator. Finish the process by clicking Next and Finish.