Restoring a Publisher Database in SQL Server

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 database
blindly. The reason we saw the errors earlier was because the sync with
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 for checking the status of this option -:  0-disabled, 1-enabled.


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 Undistributed
tab which gives the number of undistributed commands in the
distribution database waiting to be applied on the subscriber database. The Publisher
To Distributor History and Distributor To Subscriber History tabs   contain   synchronization
details between publisher-distributor and distributor-subscriber. This   helps   to
analyze the status of our restore operation.

If the sync with backup option
was disabled, you will have   encountered the error messages which we saw
earlier while restoring the database blindly and you will  have to run the sp_replrestart 
procedure on the publication database.

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.


Leave a comment

Your email address will not be published.