SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup strategy for replicated DBs


Backup strategy for replicated DBs

Author
Message
Dennis Post
Dennis Post
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1897 Visits: 547
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.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)

Group: General Forum Members
Points: 164430 Visits: 33202
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Dennis Post
Dennis Post
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1897 Visits: 547
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.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)

Group: General Forum Members
Points: 164430 Visits: 33202
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Dennis Post
Dennis Post
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1897 Visits: 547
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.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search