Backup strategy for replicated DBs

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Disaster Recovery » Backup strategy for replicated DBs

Backup strategy for replicated DBs

Rate Topic

Display Mode

Topic Options

Author

Message

Dennis Post

Posted Friday, November 23, 2012 9:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:03 AM
Points: 109, Visits: 303

I'm going on an extended holiday soon. (NZL BigGrin

)
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.... Tongue

)
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.
http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

Post #1388192

Grant Fritchey

Posted Monday, November 26, 2012 6:35 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436, Visits: 25,281

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1388580

Dennis Post

Posted Monday, November 26, 2012 7:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:03 AM
Points: 109, Visits: 303

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...

Post #1388628

Grant Fritchey

Posted Monday, November 26, 2012 7:45 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436, Visits: 25,281

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1388634

Dennis Post

Posted Monday, November 26, 2012 8:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:03 AM
Points: 109, Visits: 303

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! Smile

Post #1388642

« Prev Topic | Next Topic »

Permissions