Re: Sync production db with test

Tech-Archive recommends: Fix windows errors by optimizing your registry

Carl, I did a search of the tSQL language and found some interesting
information on Publisher/Subscriber functions where data objects are
replicated and synchronized. There is a stipulation about configuration and
introducing vulnerabilities in the db, though.

This sound intriguing for running a data warehouse and keeping n-tier
servers and fat clients' programming / scripting synchronized. Here's a bit
of code on this topic that might hold some potential, a 'merge' of data
instead of a replacement of data:

REM ----------------------Script to synchronize merge
subscription ----------------------
REM -- Creates subscription database and
REM -- synchronizes the subscription to MergeSalesPerson.
REM -- Current computer acts as both Publisher and Subscriber.
REM -------------------------------------------------------------------------------------

SET Publisher=%computername%
SET Subscriber=%computername%
SET PubDb=AdventureWorks
SET SubDb=AdventureWorksReplica
SET PubName=AdvWorksSalesOrdersMerge

REM -- Drop and recreate the subscription database at the Subscriber
sqlcmd /S%Subscriber% /E /Q"USE master IF EXISTS (SELECT * FROM sysdatabases
WHERE name='%SubDb%' ) DROP DATABASE %SubDb%"
sqlcmd /S%Subscriber% /E /Q"USE master CREATE DATABASE %SubDb%"

REM -- Add a pull subscription at the Subscriber
sqlcmd /S%Subscriber% /E /Q"USE %SubDb% EXEC sp_addmergepullsubscription
@publisher = %Publisher%, @publication = %PubName%, @publisher_db = %PubDb%"
sqlcmd /S%Subscriber% /E /Q"USE %SubDb% EXEC
sp_addmergepullsubscription_agent @publisher = %Publisher%, @publisher_db =
%PubDb%, @publication = %PubName%, @subscriber = %Subscriber%,
@subscriber_db = %SubDb%, @distributor = %Publisher%"

REM -- This batch file starts the merge agent at the Subscriber to
REM -- synchronize a pull subscription to a merge publication.
REM -- The following must be supplied on one line.
"\Program Files\Microsoft SQL Server\90\COM\REPLMERG.EXE" -Publisher
%Publisher% -Subscriber %Subscriber% -Distributor
Publisher% -PublisherDB %PubDb% -SubscriberDB %SubDb% -Publication
%PubName% -PublisherSecurityMode 1 -OutputVerboseLevel
-Output -SubscriberSecurityMode 1 -SubscriptionType
1 -DistributorSecurityMode 1 -Validate 3
Hope it's useful info,Dan Kirk

"SetonSoftware" <seton.software@xxxxxxxxxxx> wrote in message
news:cc885e81-cf0f-4c28-a1f5-0aab4c9239b6@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I'd like to set up a process so that every week our Production data
copies to our Test database. I only want the data to come over. The
table structures, indices, stored procs, UDFs, etc. currently existing
in the Test database should remain as they are.

What is the best/recommended/least painful approach to doing this?

Thanks

Carl



.