Restore Publisher Database which is part of Transactional Replication

Today I have to restore a publisher database from production backup in QA environment which is part of transactional replication in SQL Server 2005, now there are multiple ways to do it, visit this link for Microsoft recommendation.

My approach was,

  1. Stop and remove replication,
  2. restore database,
  3. recreate replication without reinitializing


Before beginning ensure that there is no pending distributed items by going to replication monitor > publication > double click the subscription > Undistributed Commands > O should be there (you can check replication tables as well to confirm).


Now First step was simple,

  • Right click Publication > Generate Scripts > Generate Drop replication and objects script > Save script to local drive
  • Right click Publication > Generate Scripts > Generate Create replication and objects script > Save script to local drive

Now execute the drop replication script, it will remove subscription, remove all jobs, and remove publication

Issues&Resolution: if it throws some error, then try to stop all replication jobs related to this publication and run script again


Moving to second step,

Here you may face some issues, so please do following first,

  • Right click database (to be restored) > Script database as > Create to > File… > save it on local folder
  • Right click database (to be restored) > Script database as > Drop to > File… > save it on local folder

Now try to restore database from backup (in my case prod backup)

It may it throw following errors,

Database is in use


Issue&Resolution: found an application user connection is continuously creating a section, tried to kill it but it did not solved issue, as app team was not available hence only solution left was to disable login, so disabled login as,


This stopped connection, but there were multiple users which are trying to make connection hence final option left to me is drop database and create a blank database and then begin restore (now the scripts taken to drop and create database will come handy). So, open script to drop database in SSMS and execute it, it may throw following error,

Server: Msg 3724, Level 16, State 3, Line 1

Cannot drop the database XXX because it is being used for replication.


Issue&Resolution: Although publication is dropped but sometimes it may not update the system tables, so execute following,

exec sp_removedbreplication ‘XXX’


exec sp_dboption ‘XXX’,’published’,false



If above method to drop database doesn’t work out then follow approaches provided at this link, http://blogs.lostincreativity.com/sqldba/how-to-drop-publisher-database-in-transactional-replication/


Once database is dropped, open database create script and execute it, it will create a blank database, Now begin restore, it should work fine


Moving to third step,

Once database is restored, then open the script taken to create publication and subscription and update the @sync_type parameter in create subscription command as instead of ‘NONE’ update it to ‘replication support only’, this will not require you to reinitialize and take snapshot, data will start syncing if everything is fine and structure is same as of previous database. Final command shall look like for read only pull subscription,


exec sp_addsubscription @publication = N’xxxxx’, @subscriber = N’xxxxx’, @destination_db = ‘xxxx’, @subscription_type = N’Pull’, @sync_type = N’replication support only’, @article = N’all’, @update_mode = N’read only’, @subscriber_type = 0


After executing the create script, you can see publication again and subscription under it and it should start sync in 10-15 mins if everything goes fine else happy troubleshooting! (in my case it worked).







Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 263 other subscribers

Translate this blog!



December 2020
« Nov    

View Post by Categories

%d bloggers like this: