Synchronizing Production Data with a Test Database on SQL Server

Thursday Apr 3rd 2003 by DatabaseJournal.com Staff

Applications developed in a development environment need to be tested in a QA environment prior to being moved to production. The QA Group needs live data from the production server to simulate implementation on the test box. Learn how to automate this production-test data sync process.

by MAK [Muthusamy Anantha Kumar]

Every corporation has a unique infrastructure environment. Let us consider a simple production, testing, and a development environment. The SQL scripts application, developed in a development environment will be tested in a QA environment and then moved to production on a production date. Usually the QA group simulates the implementation process on the test box before releasing new versions. In order to do this they need live data from the production server. One way of doing this is a simple restore backup, but there are some tweaks involved. The objective of this article is to automate this process.

The below diagram explains the process flow.

Step 1: Copy File

In practice, there will be at least one Full backup of production scheduled to run every night. Let's add a step or create a job to copy the backup file to a fileserver. You can skip this step if the backup folder in the production server is shared or if there is already a copy backup file process in place.

Step 2: Preparation

DBAs often see the following error message when restoring a database.

Server: Msg 3101, Level 16, State 1, Line 1
Database in use. The system administrator must have exclusive use of 
the database to run the restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.

In order to avoid this we need to kill the users using the database. We are going to use a small stored procedure to kill the users in a particular database. Create this procedure in the master database.

use master
--Type procedure
--author mak mak_999@yahoo.com
--date written 4/19/2000
--project maintenance
--objective procedure to kill process for a given database

create procedure usp_killprocess @dbname varchar(128) as
set nocount on
set quoted_identifier off
declare @kill_id int
declare @query varchar(320)
declare killprocess_cursor cursor for 
select a.spid from sysprocesses a join
sysdatabases b on a.dbid=b.dbid where b.name=@dbname
open killprocess_cursor
fetch next from killprocess_cursor into @kill_id
while(@@fetch_status =0)
set @query = "kill "+ convert(varchar,@kill_id)
exec (@query)
fetch next from killprocess_cursor into @kill_id
close killprocess_cursor
deallocate killprocess_cursor

--exec usp_killprocess "mydatabasename"

Create an encrypt function in the master database. This function will be useful when we are restoring sensitive data like credit card numbers, balance sheet amounts, revenue, social security numbers etc. The commonly used encrypt function is RC4. Copy this code or use your own code for the encryption function in the master database.

by MAK [Muthusamy Anantha Kumar]

Step 3: Automate the restore process

Create a job for restoring the database backup in the test server with the following job steps.

Kill Users

Add this SQL statement as the first job step, which kills all the users on the database.


Restore database

Add this SQL statement as the second job step to restore the database from the fileserver. Remember to change the database name, data and log location.

Restore database MyDatabaseName from disk = 
\\Fileserver\Sharedfolder\Product.Bak with replace,
Move "Product_Data" to "d:\mssql\data\Product_data.mdf",
Move "Product_Data" to "d:\mssql\log\Product_log.ldf"

Encrypt Sensitive data

Add these SQL statements as the third step, which encrypts sensitive data such as SSN, Credit card numbers etc.

Use Mydatabasename
Update accounts set revenue = master.dbo.encryp(revenue, char(rand()*100))
Update personal set crcardnum = master.dbo.encryp(crcardnum, char(rand()*100))
Update accounts set SSN = master.dbo.encryp(SSN, char(rand()*100))

Synchronize sysusers

In practice, production and test servers will have the same logins and users but the passwords will be different. When the production database backup has been restored in the test server, the sid column in the sysusers table will not be in sync with master table login. In order to bring it back into sync, add the script below as the fourth step.

use master
sp_configure "allow updates",1
reconfigure with override
use MyDatabaseName
update sysusers set sid = 
(select sid from master.dbo.syslogins where name = 'UserId1') where name = 'UserId1'
update sysusers set sid = 
(select sid from master.dbo.syslogins where name = 'UserId2') where name = 'UserId2'
sp_configure "allow updates",0
reconfigure with override

Step 4: Schedule the job

This job can either be scheduled daily at night or can be run on demand.


By providing an automated production-test data sync process, the QA group does not have to depend on the DBA for restoring the production data; they could run this job whenever they needed. You can use the same process for all the databases that you would like to synchronize with production data.

Mobile Site | Full Site