Introduction
- Restoring any database is a critical task that will be complicated by the database to be restored being a publisher database.
- Let us consider that you performed the restore of the database without checking that it is the publisher database.
- After the restore operation, you will probably come across below error messages.
Below is the Error Message in REPL-LogReader
Job 2022-12-01 19:04:14.538 Status: 0, code: 20011, text: ‘The process could not execute ‘sp_repldone/sp_replcounters’ on ‘server’.
2022-12-01 19:04:14.538 The process could not execute ‘sp_repldone/sp_replcounters’ on ‘server’.
2022-12-01 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 fails during startup. ‘.
2022-12-01 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’.’.
Recommended Steps When Restoring A Publisher Database
As best practice, you should follow the below steps before restoring a publisher database.
- Check whether the database is a publisher database or not. Using sp_helpdbdatabase_name will be sufficient to determine this.
This will return (amongst other details) the IsPublished status for the database.
2) Next, we need to verify what type of replication is configured on this instance. Run the 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 database.
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.
4) One important thing to do before restoring a publisher database is to verify if the database is enabled for the sync with backup option. If this option is set to true, the distribution database will only contain transactions that are backed up on the publisher database. This also means that we wouldn’t encounter the error messages that we saw earlier when we restored a publisher DB blindly. The reason we saw the errors earlier was because the sync with backup option was disabled for the publisher database, and after this was restored, the distribution database was not in sync with the publication.
Use the below command to check the status of this option: 0-disabled, 1-enabled.
select DATABASEPROPERTYEX(‘databasename’,’IsSyncWithBackup’)
5) Now, restore the publisher database. While restoring a publisher database with the sync with backup option enabled, ensure that you restore the latest full backup and the entire ensuing transaction log backup. This will help us keep the publisher and the distribution databases in sync after the restore.
6) After you have restored the database, it is important to verify the settings of the database. For this, you run sp_helpdb as in step (1) to see if the database settings remain the same.
7) One of the best uses of the Replication Monitor is for checking synchronization, see below:
After performing this we will be shown the below pop-up window:
Here, we see the UndistributedCommands tab which gives the number of undistributed commands in the distribution database waiting to be applied to the subscriber database.
The PublisherTo Distributor History and DistributorTo Subscriber History tabs contain synchronization details between publisher-distributor and distributor-subscriber.
8) This helps to analyze the status of our restoration operation. If the sync with backup option was disabled, you will have encountered the error messages that we saw earlier while restoring the database blindly and you will have to run the sp_replrestart procedure on the publication database.
9) One common misconception is about the ‘preserve the replication settings’ option which can be used while restoring. This does not safeguard a replication setup from failing. This is to be used if we are restoring the replication database on another server, with this option we could retain the replication settings.