Sql-server – SQL INSERT INTO vs SELECT INTO vs BCP in a Replication Topology(Stage Environment)

backupbcpmerge-replicationreplicationsql server

I would like to add the information from my Production DB to my Stage DB. I have a BAK of the Production DB and could just restore from that on Stage but I am concerned of the impact Merge Replication will have on that.

Let me explain further; I have 15 users testing a sometimes connected app internally. The structure is that there is a Local SQL Express on each system subscribing to the Stage SQL 2005 server with Pull Subscriptions. The Stage server acts as Publisher and Distributor. A request has been made by the testers to use "Real" data. If I just restore the BAK's from production to my Stage instance what will happen to my Replication Sets? When the Local DB's try to Sync Up will they "FREAK OUT" because all the GUIDS have changed?

My thought was to restore the Production DB to the Stage Server under a different name and then DELETE the contents of, say, tblPerson and run a INSERT INTO from the Production tblPerson to the, now empty, Stage tblPerson.

I would like thoughts and suggestions on both.

Would restoring from the BAK's cause the end of the world

and/or

is my second solution a viable alternative?

Do I even need to do that much? Can I delete the contents of tblPerson(Stage) and then do a Cross DB SELECT INTO from tblPerson(Production) to the Stage counterpart?

Mostly I am curious/concerned of the impact this will have on my existing Subscriptions.

Best Answer

You don't want to use Replication from your production to your stage environment, especially Merge replication.

Your best bet is to when ever you need need data in the staging database to backup the production database and restore the entire thing. If you try and piece meal it you'll have to deal with all the referential integrity issues that go along with that.

Related Topic