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 SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


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

Featured Database Articles

MS SQL

Posted Apr 3, 2003

Synchronizing Production Data with a Test Database on SQL Server

By DatabaseJournal.com 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
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.



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