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
- 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
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
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
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
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:
- Log in to SQL Server as an administrative user, and then
start SQL Server Enterprise Manager.
- Expand SQL Server Group, and then expand
the instance of SQL Server.
- Expand Management, and then expand
SQL Server Agent.
- Right-click New, and then click
- In the New Job Properties dialog box, type
a name for the backup job.
- Click the Steps tab, and then click
- Type a name for the step.
- In the Database list, click the name of
the SQL Server database that you want to back up.
- 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.
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.
BACKUP DATABASE Testdb TO DISK = '<drive>:\SQLBackups\Testdbbackup.bak' WITH INIT
- Click the Schedules tab, and then click
- Type a name for the schedule, click
Change, and then provide a schedule that differs from the
- Click OK to close the
Schedule dialog box.
- 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:
- Open DPM 2006 Administrator Console.
- Click the Protection menu, and then click
Create on the Actions menu.
- 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
The time that you schedule appears under Synchronize
and create shadow copies at.
- 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.
- 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
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
- 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
- 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:
- Open DPM 2006 Administrator Console.
- Click Monitoring, and then click
- 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
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:
- Open DPM 2006 Administrator Console, and then click
- On the Browse tab, find the shadow copy of
the backup file that you want. For example, find the Sqldb1.bak
- 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.
- 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: