June 17, 2008 at 8:09 am
Hi all,
I've been tasked with trying to perform a weekly .bak restore of our production database to a backup database. No problem, right? Well, here's where it gets tricky.
The production server is on a server that is hosted offsite while the backup database is on a local server, and we have a 1.5MB connection between the two sites. The .bak isn't huge, around 1.5GB, and that can shrink to around 250MB zipped so transferring the file will take about 10-15 minutes. Also, the two servers are being synchronized using transactional replication once an hour. So what I think I'm looking at needing to do is to, hopefully through a script or an SSIS job, create a backup, stop the production database, zip the .bak, copy it down to the backup server, unzip it, restore the database, and then start the production database again. I just don't really know how to do this, if it's necessary to stop the production server, or how badly this is going to affect replication.
The purpose of all this is to ensure any newly-created objects show up in the backup database, since I understand there isn't a way to automatically add articles for new objects. If I'm wrong on that count, setting that up would eliminate the need to restore the .bak file.
Any input would be greatly appreciated.
June 19, 2008 at 6:37 am
I think you can do it either way...lookup Strategies for Backing Up and Restoring Snapshot and Transactional Replication in BOL which covers what you are trying to do.
Gethyn Elliswww.gethynellis.com
June 19, 2008 at 7:03 am
Ellis, thanks for the response.
I had read that article in BOL before, but none of that is a real help to me. I'm not wanting to restore a backup of the publication database to itself, or the subscription database over itself. What I'm needing to do is restore the publication database over the subscription database in a way that won't break the replication or create missing or duplicate data in the subscription database. Reinitializing after every restore isn't a very viable option just because of the length of time the operation takes (and the need to have the subscription database available and with reliably current data).
June 19, 2008 at 7:33 am
Hi you seen this topic too...it doesn't give exact details but it did make me think that what you want to do is achievable.
Initializing a Transactional Subscription Without a Snapshot
Gethyn Elliswww.gethynellis.com
June 19, 2008 at 7:48 am
I have, but I hadn't thought about applying it to this situation. I'm already pulling a backup file down every night anyway, and if I don't have to worry about necessarily taking a backup immediately before restoring the backup database, I can just use that and let replication fill in the blanks on its next cycle.
Thanks, I'll give this a run either over this weekend or next.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply