Synchronizing Production Data with a Test Database on SQL Server
April 3, 2003by 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 go --Type procedure --author mak firstname.lastname@example.org --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.