Backup User Databases Using a Maintenance Plan

Data is the heart and soul of any enterprise. It has become an essential task for organizations around the world to protect their data. Database Administrators have a tough job to implement right database backup strategies and disaster recovery plans. Backing up your user databases can protect an organization against the accidental loss of data, database corruption, hardware/operating system crashes or any other natural disasters.  Unfortunately if you don’t have proper database backups then you are left with nothing to fall back on. As a DBA you need to make sure that the databases are backed up regularly and the backup tapes are stored in a secure location. In this article you will see how to utilize Maintenance Plans in SQL Server 2008 to take Full, Differential & Transactional Log backups of AdventureWorks database. If you are very new to Maintenance Plan then you can refer to my previous article titled Overview of Maintenance Plans in SQL Server 2008 to know different types of maintenance plan tasks and their usage.

Permissions Required to Create & Manage Maintenance Plans
Maintenance Plans are only displayed to those users who are connected to the SQL Server using Windows Authentication Mode. Moreover, you need to be a member of sysadmin fixed server role to create and manage maintenance plans. The maintenance plans on the SQL Server are visible only to those users who are member of sysadmin fixed server role.

Using Maintenance Plans to Backup User Databases
In this article you will be creating a Maintenance Plan using Maintenance Plan Wizard to take Full, Differential and Transactional Log backup of AdventureWorks databases. As AdventureWorks database uses Simple Recovery Model, transactional log backups is not possible. So you first need to change the Recovery Model of AdventureWorks database to Full using the below mentioned TSQL. To know more about different recovery models in SQL Server you can refer to my previous article titled Database Recovery Models in SQL Server.

Use master
GO

ALTER DATABASE AdventureWorks
SET RECOVERY FULL
GO

1. Connect to SQL Server 2008 Instance using SQL Server Management Studio.

2. In the Object Explorer, Click on Management and right click Maintenance Plans to open up the popup windows as shown in the below snippet. In the popup windows you need to select Maintenance Plan Wizard.


 
3. In the Maintenance Plan Wizard click next to continue with the wizard.

 

4. In the Select Plan Properties screen you need to provide the name & description for the maintenance plan. In the same screen there is an option available to choose the schedule. You can either have different schedules for each task or you can choose a single schedule for the all the tasks. However since this maintenance plan is used to perform Full, Differential and Transactional Log backups of AdventureWorks database you need to choose the option for schedule as Separate schedules for each task. Click Next to continue with the wizard.