Home / server / Questions / 52453
Accepted
Refracted Paladin
Asked: 2009-08-11 13:12:35 +0800 CST

SQL INSERT INTO vs SELECT INTO vs BCP in a Replication Topology(Stage Environment)

  • 772

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.

sql-server backup replication merge-replication bcp

3 Answers

  1. Restoring the database will destroy your replication (at least, it has in my scenarios. If anyone knows different please tell me!

    We did pretty much what you're suggesting - DELETE * FROM myTable and the INSERT INTO. It took a while, but dropping tables or dropping/restoring the database destroyed the replication in our scenario.

    I don't know if it will matter, but we also paused replication on all the subscribers whilst doing this.

    • 1
  2. 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.

    • 1

  3. Either INSERT INTO or BCP would probably work. As you mentioned, restoring the production database would require that you recreate your replication in your staging instance. SELECT INTO isn't a good idea because you can't use an existing table as the target of the INTO clause - it has to be a new table - so you'd have to delete the destination table, which again would require repairs to your replication.

    Of the other two, INSERT is definitely the simplest, and I've used that method frequently. If speed is required, try BCP. Bulk inserts are typically a lot faster than a standard INSERT operation. According to BOL (http://msdn.microsoft.com/en-us/library/ms151206.aspx), you have to use a switch to force the triggers to fire, but it sounds like it should work fine otherwise.

    • 1