Synchronizing Production Data with a Test Database on SQL Server

by MAK [Muthusamy Anantha Kumar]

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 [email protected]
–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 [email protected]
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.

Latest Articles