How to run sp_browsereplcmds on a Distribution db backup

How to run sp_browsereplcmds on a Distribution db backup

Chris Skorlinski
Microsoft SQL Server Escalation Services

From time to time we need to restore a customer’s Distribution database to a secondary server or to a lab server for testing.  When you restore a distribution database it will not be configured to execute Replication commands such as sp_browsereplcmds.  However, there is a simple solution.  You can execute the command below to configure the restored distribution database as a “Replication Distribution” database.

sp_adddistributiondb [distribution_A]

Once configured, the restored database shows up in the Distributor properties





You can now run commands such as sp_browsereplcmds to explore data in the Distribution database

sp_browsereplcmds  ‘0x0002D17D00021373001100000000’, ‘0x0002D17D00021373001100000000’

Comments (1)

  1. Jonathan (CTS) says:

    I've also simply casted the command column as nvarchar and returned the results in text.  Its not as clean as what Chris proposes above, but it is a bit faster:

    select top 10 CAST(command as varchar(500))as text from distribution.dbo.msrepl_commands