How to Manually Create a SQL Replica in DPM 2012 R2

First published on TECHNET on Mar 21, 2017

There may be circumstances where creating SQL replica automatically over the may not be possible due to bandwidth constraint between server and (situations where and protected server are in different sites). In such situations we can rely on manual replica creation process. This lets us create a replica on the server without transferring the data from the protected server. Data can be copied to a USB drive and then exported over to DPM server and then copied over to  the replica path.

Here is a procedure for creating manual replica for SQL protection in DPM.

Note: This involves stopping Service to copy database and log file from the (These files are in use and cannot be copied unless SQL Server service is stopped).

1.  On the DPM console click on New to create new protection group.

2.  Select Servers and click Next.

3. Expand the SQL server listed under the domain and select the database to be backed up. In my lab it's named TestDB.

4.  Name the protection group and hit Next.

5. Specify Retention range, Synchronization frequency, and backup schedule and click Next.

6. Leave the values to default and hit Next.

7. On the next page select “Manually” from under “Replica in DPM Server” and click Next.

8. Leave the options to default selection and click Next.

9. Next page will show summary of the settings and data selected. Click on “Create Group”.

The next page will show you the Allocate replica and protection group creation status. Close the page.

1o. Note that SQL protection shows Manual Replica Creation Pending state. Click on “Click to view details” next to Replica path.

11. You will see a pop window showing Replica path. Copy this path. This is path which you will use later to copy the database files to.

In my lab I have copied the following path.

C: on               C:Program FilesMicrosoft System Center 2012 R2DPMDPMVolumesReplicaSqlServerWriterVol_ba644616-05c4-4f06-bc8e-05ed4bd8ef4686740339-0e6f-4c7b-8bf3-28682d532fc9FullC-Vol

12. Now we need to copy the database files from the SQL server to any USB disk. Log on to your SQL server and open SQL Server Management Studio. Right click on the database which needs to be protected and click on Properties. On the Database properties page click on “Files”. This will show you the path for your database files. Note down the path to files(from the screenshot we have Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA). Later we will need to add this directory structure to replica path and then copy files.

Stop the SQL Server (MSSSQLSERVER) service and navigate to the path to copy the database and log file. In my lab its named TestDB.mdf and TestDB_log.ldf.

13. On the DPM server download PSExec (

) and extract the files. Open command prompt and navigate to PSTools folder and run the following command.

psexec -s -i cmd.exe

Above command will open a new command prompt in System context. This will help in creating directory structure and navigating to replica path to copy  files.

Now we need to add Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA directory structure to replica path. So replica path will look like  C:Program FilesMicrosoft System Center 2012 R2DPMDPMVolumesReplicaSqlServerWriterVol_ba644616-05c4-4f06-bc8e-05ed4bd8ef4686740339-0e6f-4c7b-8bf3-28682d532fc9FullC-VolProgram FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA.

Use Mkdir – p command to create desired directory structure.

Use Robocopy to copy the database file to replica path. I placed the copied over database and log file to C:SQLfiles on DPM server and ran following command to copy the files to replica path.

Robocopy “C:SQLfiles” “C:Program FilesMicrosoft System Center 2012 R2DPMDPMVolumesReplicaSqlServerWriterVol_ba644616-05c4-4f06-bc8e-05ed4bd8ef4686740339-0e6f-4c7b-8bf3-28682d532fc9FullC-VolProgram FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA” /Copyall

14. Once the copy is complete you can run consistency check on the datasource.

Once Consistency check completes it should show the Protection Status as ok.

I hope the above steps will help you create a manual replica in situations where you cannot transfer the data over the due to a slow network.

Jitendra Mishra

Microsoft Support Escalation Engineer


This article was originally published by Microsoft’s System Center Blog. You can find the original article here.