Backup strategy for replicated DBs

Question

Backup strategy for replicated DBs

Dennis Post

SSCarpal Tunnel

Points: 4473
More actions
November 23, 2012 at 9:03 am

#266326

I'm going on an extended holiday soon. (NZL :-D)
It's so happens that I had to restore this week.
Combining the 2 previous sentences, I came up with a question.
Who will do restores (If needed) when I'm away?
Turns out none of my colleagues knew how. So, "How to" documentation time! All is well again, until the question :
But how will that affect the replication?
After a bit of research, I couldn't give a proper answer.
As far as i'm concerned you don't overwrite DBs when restoring. Rename and/or recreate the publications via "Generate Scripts...".Therefore you shouldn't need to take any extra steps. But according to MS then you should restore the system databases at the time you restore your user DB.
This sounds a bit dodgey to me. What about all the changes to any other DBs or jobs changed created between the backup of the troublesome DB and the point it needs to be restored to?
One DB per instance? Doesn't sound very handy.
So what am I not getting here. I have never had to do File restores so don't know how this fits in.
Never restore using REPLACE? When testing this, it worked ok on a subscriber. Replication kicked in again after updating a published table.
Restoring a publisher with REPLACE, KEPP_REPLICATION did not go so smoothly. Had to recreate the replication.
Does anyone ever restore a DB with overwrite? Or does this just earn you a spanking?
Now i'm not sure if our backup strategy is sufficient.
Your wisdom and experience in this would be much appreciated.
(Sorry about the long post, got a bit carried away....:-P)
Resources:
http://msdn.microsoft.com/en-us/library/ms151152.aspx
http://msdn.microsoft.com/en-us/library/ms151152%28v=sql.90%29.aspx
http://msdn.microsoft.com/en-us/library/ms152560.aspx

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]
"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

But according to MS then you should restore the system databases at the time you restore your user DB.

I've never seen this one unless you're doing a system restore, not a database restore. If you're rebuilding an instance, yeah, you'll need to restore system databases. If you're just restoring a database, restore the database. Don't mess with the system DBs at all.

As to replication and overwriting a database. Well, the easy part first, yes, I restore databases with overwrite all the time. That's not an issue, if you want to overwrite that database. As far as replication goes, this gets very tricky. If you're restoring a publisher and you're in transaction replication, you're going to have to rebuild the replication including a snapshot, etc., because you no longer have the two databases in proper syncronization. If you're in merge replication... you might be able to rely on the merge to fix up the databases after the restore (good luck). If you're in snapshot, nothing, another snapshot will be taken. But, all this needs to be tested & validated within your environment (although obviously not on the production systems).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 2

Hi Grant,

Thanks for taking the time to share!!

Have you ever had to restore a published DB? Did/would you generate the scripts (Via the GUI) for the publications just prior to restoring? This is what I've been doing in my dev area. (Replacing the NULL passwords)

Although I pump replication data into a DBA DB, I can only use that data to recreate a publication manually. Ideally, I would want to script out the create publication scripts automatically, say once a week to file. Or is this going overboard? Will I have to script this from scratch or has MS got a handy little sp for this?

Profiler shows many SPs when I "Generate script".

exec sp_helparticle @publication = N'Testing_Pub'

exec sp_helppublication @publication = N'Testing_Pub'

exec sp_helpsubscription @publication = N'Testing_Pub', @article = N'CaseTest'

exec sp_helpsubscription @publication = N'Testing_Pub', @article = N'FKCompKey'

-- etc...

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

All my experience with replication was transaction replication. In that, if we modified the publisher outside of normal data access, we had to completely rebuild the publication. It was a royal pain. It's one of the reasons I started using mirroring. A lot less effort there. Of course, you don't get read copies unless you're in 2012 and using Availability Groups, so, before 2012, replication still has a use, but one I try to avoid where possible.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Dennis Post SSCarpal Tunnel Points: 4473 More actions · Answer 4

We'll be making the move to 2012 and availibility groups sometime next year. Hopefully i'll get a 2012 dev environment well before crunch time.

Thanks for your input! 🙂

For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.[/url]

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort