1

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.

Refracted Paladin
  • 345
  • 7
  • 19

3 Answers3

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.

Mark Henderson
  • 68,316
  • 31
  • 175
  • 255
1

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.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • I beliewe there has been a miscommunication. I do not now and do not propose as a solution using Replication between Production and Stage. What I meant was that I have Replication set up on those Servers to the Local Users. As to your second part; that is what I am worried about in my original question. If I restore Stage with Production, all the rowguids will be different and I am concerned what will happen to the local subscribers when this happens. Sorry I didn't make myself clearer. – Refracted Paladin Aug 11 '09 at 03:57
  • 1
    If you were to restore prod to stage with the KEEP_REPLICATION flag in place (then hack the replication tables to get it up and running again) you would need to repush the snapshot back out to your subscribers. When you do the restore the rowguid values won't be changed except for the rows that didn't exist before (hence the new snap). – mrdenny Aug 11 '09 at 19:18
  • I didn't know about `KEEP_REPLICATION`...Is that on restore or on backup. Your verbiage makes it sound like on Restore, which would be great. Can you please elaborate on the "hack the replication tables..." portion? Thanks – Refracted Paladin Aug 11 '09 at 19:34
  • 1
    It's a flag which is used when restoring the database. After you move the publisher from prod to stage, you'll need to modify the replication tables on the publisher so that it'll talk to the staging distributor. I don't remember which tables you'll need to change, but there aren't that many to look through. – mrdenny Aug 11 '09 at 20:05
  • Oh, so if I don't want to move the publisher to Stage `(which I don't)` but just the data that is on Production`(which I do)` then I shouldn't have to do any "hacking..."? – Refracted Paladin Aug 11 '09 at 20:10
  • If you backup your production database, and that database is replicated then you are copying your publisher from production to staging. If you have replication setup in stage with the database you are going to backup and restore as the publisher for that replication topology then you'll need to edit the tables to get the replication working in staging again. – mrdenny Aug 11 '09 at 20:49
1

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.

Ed Leighton-Dick
  • 1,094
  • 1
  • 7
  • 12