Synchronizing Production Data with a Test Database on SQL Server

April 3, 2003

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
go
--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)
begin
set @query = "kill "+ convert(varchar,@kill_id)
exec (@query)
fetch next from killprocess_cursor into @kill_id
end
close killprocess_cursor
deallocate killprocess_cursor

--usage
--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.