It is essential to create and maintain backups in any database management system. There is a range of scenarios that confirm this statement. Hardware failures, software or user errors, malicious attacks, or system issues can lead to a corrupted database. Even if your database management systems have never experienced such scenarios, you have to be prepared

Backup strategies should be planned and documented in advance. It’s important to identify and classify areas of the database management system by importance and the corruption risk level. Also, any database backup strategy should be tested and verified in appropriate environment and time scenarios – a failed backup process is equal to no backup process

SQL Server provides the Maintenance Plans feature via SQL Server Management Studio which makes execution of backup strategies easy and automated. In the SQL Server Maintenance Plans – benefits, features, and functionalities article we described general characteristics, methods, and properties that can be used to automate common database maintenance tasks. In this article, we’ll focus on the Back Up Database task

There are two methods to create a SQL Server maintenance plan with the Back Up Database task within – a manual and via the Maintenance Plan Wizard feature. Note that one maintenance plan can hold a number of additional tasks (e.g. Check Database Integrity, Shrink Database, etc.) at the same time, in appropriate execution order, if required

Create a database back up task manually

To create a maintenance plan in SQL Server Management Studio with appropriate database back up task:

1. In the Object Explorer tree, using the context menu of the Maintenance Plans folder, select the New Maintenance Plan option
2. Type in the name of new maintenance plan (e.g. BackupMaintenancePlan)

3. Using the Maintenance Plan Tasks toolbox, add the Back Up Database Task item (task) to the BackupMaintenancePlan design surface

   

4. Once added, the backup task is set with default values, such as the BackupAction (Database), BackupDeviceType (File), UseExpiration (False), and so on

   There are more than 60 properties on the back up task Properties pane that can be used for defining and fine-tuning the task. Using the Properties pane set the properties per your backup strategy

   

5. Additionally, use the Edit option of the Back Up Database Task item on the design surface to open the Back Up Database Task dialog

   

   The dialog provides a more convenient method for configuring the backup task properties than the Properties pane, but not all of the properties can be set via the dialog (e.g. the TimeOut, or ObjectTypeSelection property)

6. If needed, use the steps 3 to 5 to add additional backups or other task types to the maintenance plan
7. Use the Subplan Schedule option to set the schedule for the task(s). The option will open the standard New Job Schedule dialog, as used by the SQL Server Jobs feature. Additionally, use the Add Subplan option to add subplans to the maintenance plan in order to group maintenance tasks per schedule time
8. Once the task(s) and properties are set, save the maintenance plan (BackupMaintenancePlan). This will, at the same time automatically create the corresponding job (BackupMaintenancePlan.Subplan_1) in the Jobs sub node of the SQL Server Agent node. You can use the job to set additional actions, such as alerts and notifications (e.g. via email)

Create a database back up plan using the wizard

SQL Server Management Studio provides an additional wizard-like method to create a database back up plan. This method is more convenient than the manual one, but is provides less fine-tuning options. However, if your backup strategy doesn’t require additional tuning, it might be the right solution

To create a maintenance plan with an appropriate database back up task via the wizard:

1. In the Object Explorer tree, select the New Maintenance Plan option from the context menu of the Maintenance Plans folder

2. The Select Plan Properties dialog will open and provide initial settings, such as a name and schedule options

   

3. In the Select Maintenance Tasks dialog, select the required back up database tasks (Full, Differential, or Transactional Log). Of course, any additional maintenance task can be selected too

   

4. Using the Select Maintenance Task Order dialog, set up the execution priorities of the tasks
5. Depending on the selected tasks, the wizard will continue with appropriate dialogs (e.g. for the Back Up Database task, the wizard will provide the same dialog as described in the Create a backup task manually section)

6. After the wizard is finished, a new maintenance plan is created with the appropriate subplan(s) and job(s). Note that if the Separate schedules for each task option is selected in the Select Plan Properties dialog, multiple subplans and corresponding jobs will be created when the wizard is completed

   Once created, the database maintenance plan can be modified using the previously described manual method – the wizard can be used only to create, not to modify existing plans

Although the SQL Server maintenance back up plans provide viable advantages, such as GUI and native schedule support, there are several disadvantages that DBAs should be aware of. The Maintenance Plans feature (prior to SQL Server 2012 version) uses the sqlmaint utility instead of native SQL Server backup operations. Also, it’s required to maintain both database back up maintenance plans and corresponding jobs in order to preserve consistency and to prevent back up process collisions. Moreover, there is no collision detection and a task execution will fail if concurrent tasks are scheduled on the same database(s) at the same time. In order to avoid such scenarios, it’s advisable to test execution of maintenance plans before they are left in charge of your backup strategy