How to use Microsoft System Center Data Protection Manager 2006 to help protect a SQL Server database

This article describes how to use Microsoft System Center Data Protection Manager (DPM) 2006 to help protect a Microsoft SQL Server database. The methods that this article describes are for the following products:

• Microsoft SQL Server 2005
• Microsoft SQL Server 2000
• Microsoft SQL Server 7.0

DPM 2006 cannot directly help protect a SQL Server database. Before you can use DPM 2006 to help protect the SQL Server database, you must back up the SQL Server database to a file. You must store this file on a share or on a volume that is a member of a DPM 2006 protection group. You can perform an online backup of the SQL Server database by using SQL Server Enterprise Manager or a third-party backup solution.

Note The methods that this article describes were tested by using SQL Server Enterprise Manager. If you select to use a third-party backup solution, follow the recommended third-party procedures for backing up and restoring the SQL Server database before you use DPM 2006 to help protect the SQL Server database.

For more information about how to back up the SQL Server database, see the "Backing up and restoring databases" topic in SQL Server Books Online, or visit the following Microsoft Web sites:

How to back up a .bak file by using SQL Server Enterprise Manager

To use DPM 2006 to help protect the SQL Server database, you must first back up the SQL Server database to a file. Then, you must create a protection group to help protect the volume, the folder, or the share that contains this backup file.

Back up the SQL Server database to a file

Configure SQL Server Enterprise Manager or a third-party backup solution to perform a typical scheduled backup of the SQL Server database to a file in one of the following locations:

• A local volume, such as <drive>:\SQLBackups\TestdbBackup.bak
  
  Note To avoid a decrease in performance of SQL Server, this volume should not be the same volume that contains the SQL Server database logs or the SQL Server database.
• A network share, such as \\DumpFileServer\SQLDumps\Sqldb1.bak

Schedule the backup job to run at a preferred time every night. For example, schedule the backup job to run at 23:00.

Note This schedule is just an example. If you decide to change this time, consider scheduling the backup job to run before the time that you synchronize and create shadow copies.

To create a backup job in SQL Server Enterprise Manager, follow these steps:

1. Log in to SQL Server as an administrative user, and then start SQL Server Enterprise Manager.
2. Expand SQL Server Group, and then expand the instance of SQL Server.
3. Expand Management, and then expand SQL Server Agent.
4. Right-click New, and then click Job.
5. In the New Job Properties dialog box, type a name for the backup job.
6. Click the Steps tab, and then click New.
7. Type a name for the step.
8. In the Database list, click the name of the SQL Server database that you want to back up.
9. In the Command box, type the backup command, and then click OK.
   
   For example, if the SQL Server database that you want to back up is named Testdb, type the following command.

   BACKUP DATABASE Testdb TO DISK = '<drive>:\SQLBackups\Testdbbackup.bak' WITH INIT

   Note Because you are backing up the full database, the INIT option makes sure that the backup file is not appended to the file that is used for the previous backup job. For example, this article assumes that DPM 2006 will be configured to help protect the <drive>:\SQLBackups folder on the production server. If you back up the SQL Server database to a different folder, make sure that DPM 2006 is configured to help protect that folder.
10. Click the Schedules tab, and then click New Schedule.
11. Type a name for the schedule, click Change, and then provide a schedule that differs from the default schedule.
12. Click OK to close the Schedule dialog box.
13. Click OK to close the New Job Properties dialog box.

Create a protection group

To help protect the backup file that you created in the "Back up the SQL Server database to a file" section, use the New Protection Group wizard to create a new protection group. To do this, follow these steps:

1. Open DPM 2006 Administrator Console.
2. Click the Protection menu, and then click Create on the Actions menu.
3. On the Select Protection Schedule page of the New Protection Group wizard, click Specify Schedule, and then schedule a shadow copy to be created one hour after the backup job should be finished.
   
   The time that you schedule appears under Synchronize and create shadow copies at.
4. Schedule a second shadow copy to be created several hours after the first shadow copy is created.
   
   Allow for several hours because you must schedule a consistency check between these two shadow copies. For example, if the backup job starts at 23:00, schedule the first shadow copy to be created at about 01:00. The backup job should be finished by 01:00. Then, schedule the second shadow copy to be created at 06:00. The consistency check that is described in the next step should be finished by 06:00.
5. On the Select Protection Schedule page, click Advanced Options, and then click Schedule daily consistency check. Specify a start time for the consistency check that is at least one hour after the shadow copy in step 3 and at least four hours before the shadow copy in step 4. Set the maximum duration as four hours.
   
   This step assumes that your consistency check will finish in four hours. If you determine that your consistency check takes longer, increase the maximum duration accordingly, and change the schedule of the shadow copy in step 4. Based on the example in step 4, schedule the consistency check to start at 02:00.

Notes

• You may receive two messages every day, one for a failed synchronization and one for a failed shadow copy. These messages indicate that the replica that was used to help protect the backup file is invalid after the first shadow copy is created. However, this behavior is expected. These messages automatically become inactive after the consistency check runs and finishes.
• In step 4, schedule the second shadow copy to be created one hour after the consistency check should be finished. To verify that the synchronization of the folder or of the share is finished, follow these steps:
  1. Open DPM 2006 Administrator Console.
  2. Click Monitoring, and then click Jobs.
  3. View the synchronizations list, and then verify that the synchronization of the folder or of the share is finished.
• Make sure that the Shadow Copy Area entry in the disk allocation for the protection group is sufficient to hold multiple shadow copies. If this entry is insufficient, you may lose previous shadow copies.

You can use this process if you intend to back up the SQL Server database one time each day. If you must back up the SQL Server database more frequently, you can adjust your backup schedule and the protection group schedule for synchronization and shadow copies to fit your needs.

How to restore the SQL Server database

If you must restore the SQL Server database backup file that is being protected, follow these steps:

1. Open DPM 2006 Administrator Console, and then click Recovery.
2. On the Browse tab, find the shadow copy of the backup file that you want. For example, find the Sqldb1.bak file.
3. After you find the backup file that you want, click Recover, and then restore the backup file to the target.
   
   If the original Sqldb1.bak backup file still exists on the restore target server, the recovered copy is renamed. For example, the recovered copy may be named Copy of Sqldb1.bak.
4. After you have the backup file, perform SQL Server database recovery by using SQL Server tools or a third-party backup solution.

For more information about how to restore the SQL Server database, visit the following Microsoft Web sites: