Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 3, 2003

Synchronizing Production Data with a Test Database on SQL Server

By Staff

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

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM

Thanks for your registration, follow us on our social networks to keep up-to-date