Restoring the backup of a publisher

Question

Restoring the backup of a publisher

Nimesh T Mathews

Right there with Babe

Points: 727
More actions
January 14, 2008 at 3:12 am

#119680

Hi All,
I need to restore a production backup of a database in our test environment. The database in test environment is a publisher and is part of the replication. How can i restore the backup with out affecting the replication. or do i need to reconfigure the replication from scratch.
Regards
Nimesh

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

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

As data on Production and Test enviroment is different. You need to reconfigure replication after the restore. If you are sure that there are no change in database design. You can run the Sanapshot and start the replication.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

I think u have remove the publication first before restoring the backup.

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 3

You don't always have to remove the publication from your production database prior to backup, but when you come to restore it in your test environment, be prepared for some fun and games to re-enable replication in your test environment.

What I've done in the past is script the publication on the test system first, remove any subscriptions, then restore your publication database. There are some views held on the publication database that relate to replication following your restore (SYNC_ ) and these will need to be dropped manually. Then run your script to recreate the publication and recreate the subscriptions.

It's not a pretty way of doing things and someone may know a better way of getting round this, but the above seems to work for me.

Nimesh T Mathews Right there with Babe Points: 727 More actions · Answer 4

Hi,

I did the following to resolve the problem

Steps while restoring a publisher

• Take a native backup (with out using LiteSpeed) of the database to generate the backup file for restore

• Select the publisher database and script the publication.Script the publication by going to Database -> Publication ->right click – generate SQL Script

• Restore the database using the following command

Restore Database

From disk = ‘ ’,

With move ,

Keep_Replication,

Stats =5

• Fix the orphaned logins

• Drop the subscription by executing the following command

sp_dropsubscription@publication = N' ',

@article = N'all',

@subscriber = ‘

• Drop the Publication by

sp_droppublication@publication = N' '

• Execute the script that we already created.

• Restart the sql server agent service to resume the jobs configured for replication

• Run the snapshot to reinitialize the subscriber (if any article changes are there)

• If the replication monitor is red in color, please execute this command to clear the logs.

sp_MSLoad_replication_status