MICROSOFT SQL SERVER
SQL Server backups made easy using Maintenance Plans
Greg Robidoux, Contributor

If you're new to SQL Server and not sure how to set up backups, Maintenance Plans may be the way to go. Although not all of the backup functionality is available using Maintenance Plans, Microsoft has developed an easy interface to set up backups for all of your databases.

Maintenance Plans give DBAs an easy way to run database maintenance procedures. With Maintenance Plans, you can run database backups, transaction log backups, index rebuilds, stats rebuilds, database consistency checks and shrink a database. Since our focus is on database backups, we will talk about those features.

You can use the SQL Server wizard to build a maintenance plan. Start the wizard by clicking the Run a Wizard button in Enterprise Manager and selecting Database Maintenance Plan Wizard under Management, or right-click Database Maintenance Plans under Management in Enterprise Manager. There are also stored procedures to create maintenance plans, but the real benefit to using Maintenance Plans is the GUI. If you know how to create jobs and tasks using T-SQL, I would avoid using Maintenance Plans and create your own process.

The wizard is a simple step-by-step operation in which you check off a few items; the maintenance plan is created once you save it. Depending on which options you select, one or more jobs are created in SQL Agent. After you create the plan, you can view and edit the plan information. The edit feature breaks each task into separate tabs as shown below.

As you can see, a Database Backup (complete backup) and a Transaction Log backup have identical features available. The options are pretty much self explanatory, and the key option here is how you setup your backup schedule. Refer to the tip Selecting a SQL Server backup model for additional information.

When the maintenance plan is saved, the information about the plan is stored in these system tables in the MSDB database. You can query the individual tables, but the information is not all that helpful unless you join tables:
  • sysdbmaintplan_databases
  • sysdbmaintplan_history
  • sysdbmaintplan_jobs
  • sysdbmaintplans

The easiest way to access the data is by using the GUI -- or use the stored procedure sp_help_maintenance_plan. Unfortunately, the only parameter for this stored procedure is plan_id. This column is a uniqueidentifier and looks something like this: 0F6E3659-A300-42D2-986D-046CD9EAB070, which is not the easiest thing to type or remember, so you are forced to query the tables to get this value. Also, once you run this stored procedure, the results are not all that helpful, so stick to the GUI.

After you save the plan -- and depending on the tasks you selected -- one or more SQL Server Agent jobs are created. If you do a complete backup and transaction log backup, two jobs will be created. The jobs will use the name of the Maintenance Plan along with some additional information to create the job name.

The tasks in the jobs look something like the following commands.

  • Complete Backup
    • EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0F6E3659-A300- 42D2-986D-046CD9EAB070 -WriteHistory -VrfyBackup - BkUpMedia DISK -BkUpDB -UseDefDir -BkExt "BAK"
 
  • Transaction Log Backup
    • Transaction Log Backup
      • EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0F6E3659-A300- 42D2-986D-046CD9EAB070 -WriteHistory -VrfyBackup - BkUpMedia DISK -BkUpLog -UseDefDir -BkExt "TRN"'

    You would expect to see a BACKUP command somewhere in this task, but instead it uses a utility called sqlmaint. The advantage to using sqlmaint is that all of the other features for a Maintenance Plan are passed as parameters. The downside: Now you need to learn another set of syntax to run your backups.

    Summary

    Here is a short list of advantages and disadvantages of using Maintenance Plans:

    Advantages

    • GUI to set up backups
    • Can delete old backup files easily

    Disadvantages

    • Uses sqlmaint instead of native backup commands
    • Cannot do differential backups
    • Impossible to remember Plan_id
    • Need to maintain both a job and a maintenance plan

    If you are new to SQL Server and not sure how to run backups, then using a Maintenance Plan is the place to start. Having some type of backup is better then not having anything at all. But don't stop there; take the time to learn the Backup and Restore commands and build a more robust backup solution. Anything you can do with a Maintenance Plan can be duplicated and probably made better to meet your needs. Maintenance Plans are an easy way to get things going, but invest the time to learn all of the backup features, so you know them when you most need them.

    Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Greg, who serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.