Restoring a Publisher Database in SQL Server


Restoring any database is a critical task which will be  complicated
by the database to be  restored being a publisher database. For the purposes of
this article, I will assume familiarity with the different types of replication
and their features. All   examples in this article  are with respect to
transactional replication.

Let us consider that you performed the restore of the
database without checking that it is the publisher database. After the restore
operation, you would probably come across below error messages.

Below is the Error Message in REPL-LogReader Job

2010-03-29 19:04:14.538 Status: 0, code: 20011, text: ‘The
process could not execute ‘sp_repldone/sp_replcounters’ on ‘server’.

2010-03-29 19:04:14.538 The process could not execute
‘sp_repldone/sp_replcounters’ on ‘server’.

2010-03-29 19:04:14.538 Status: 0, code: 9003, text: ‘The
log scan number (37:396:4) passed to log scan in database ‘dbname’ is not
valid. This error may indicate data corruption or that the log file (.ldf) does
not match the data file (.mdf). If this error occurred during replication,
re-create the publication. Otherwise, restore from backup if the problem
results in a failure during startup. ‘.

2010-03-29 19:04:14.538 Status: 0, code: 22017, text: ‘The
process could not set the last distributed transaction

2010-03-29 19:04:14.538 Status: 0, ode: 22037, text: ‘The
process could not execute ‘sp_repldone/sp_replcounters’ on ‘server’.’.

You may  have noticed that the log reader agent has failed. The
log reader agent job is the interface between the publisher database and the
distribution database. From the error message, it is evident the data in
publisher database is out of sync with the data in distribution database. In
order to overcome this error, we could use the stored procedure sp_replrestart
which  needs to be run on the publisher database which you
restored. This stored procedure should only be used in the case of
transactional replication. 

After this, you could try stoppingstarting or refreshing the
log reader agent job and the distribution agent jobs. On checking the replication
monitor, you would see that the replication is back online again. Below is the
screenshot of the jobs for your reference. In the job activity monitor, you
would see them under the category column as shown. .  

Recommended Steps When Restoring A Publisher Database

As best practice, you should follow the below steps before
restoring a publisher database.  

1) Check
whether the database is a publisher database or not.
Using  sp_helpdb database_name
 will be sufficient to determine this. This will return (amongst other details)
the IsPublished status for the database.

Next we need to verify what type of replication is configured on this
instance. Run below command from Management Studio on the publisher database. :

select * from syspublications

In  the description column you will
 be shown  the type of replication:

Similarly, you could run the below
query on the publisher database to find the details of the subscribing

select * from syssubscriptions

3) As   best
practice, before we start the restoration, we should script out the replication
 which will  help us to re-perform the replication if required at a later
point.  Right click on the replication folder and select ‘Generate Scripts’

 Ensure that you script out
commands to enable   replication components and also script the replication
jobs as shown below.


Pages: 1 2


No comments yet... Be the first to leave a reply!