Using a Maintenance Plan to Backup SQL Databases

Maintenance Plans are only displayed to those users who are connected to the SQL Server using Windows Authentication Mode.

I will use AdventureWorksDW database which uses Simple Recovery Model and hence transactional log backups is not possible.

We first need to change the Recovery Model of AdventureWorks database to Full using GUI/TSQL.

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. In the popup windows you need to select Maintenance Plan Wizard.

In the Select Maintenance Tasks screen you need to choose Back up Databases (Full), Backup Databases (Differential) and Back up Database (Transactional Log) as shown in the below snippet and then click Next to continue with the wizard.

This will pop up a screen to Select a database you want to backup.

Then you need to select the option Create a backup file for every database, next select the checkbox, Create a sub-directory for each database checkbox and then provide the folder location where you want the databases backups to be stored along with the file extension as “diff”. If you are interested in verifying backup integrity then you can select Verify backup integrity option.

Click on “Change” to schedulethe the job & timing

Define where you want to store log files on the server.

In Complete the Wizard screen you could see the summary of all the options which you have selected so far in the maintenance plan wizard,

In the Maintenance Plan Wizard Progress screen you need to make sure that all the tasks have completed successfully and then click Close to complete the wizard.

If you want to run the backups manually just navigate to SQL Serevr Agent -> Job Activity Monitor . Right click on the job and select “Start Job”

I can now see a successful backup fie at “D:\SQL_TRAN_LOG\backups\AdventureWorksDW”