How to restore replication after server crash

We had two databases on one SQL Server 2005 instance with transactional replication between them (3 tables as articles). This server acted both as publisher and distributor.

Then the RAID on the server failed. However we managed to detach and copy almost all .mdf files before the crash.

After the system restored, we reinstalled SQL Server, restored system databases (master, model, msdb) and placed the .mdf files to the same paths. So it started.

The problem appeared with the replication. The Local Publications folder was empty, though the Local Subscriptions folder contained the subscription in question. When I tried to add a new publication I got:


New Publication Wizard encountered one or more errors while retrieving
publication names.


An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)


Invalid object name 'dbo.syspublications'. (Microsoft SQL Server,
Error: 208)

I decided to try to remove the left subscription (eventually I managed to) and disable publishing and distribution on the server, which gave me:


SQL Server could not disable publishing and distribution on
'someserver'.


Invalid object name 'dbo.syssubscriptions'. Transaction count after
EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is
missing. Previous count = 0, current count = 1. Transaction count
after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION
statement is missing. Previous count = 0, current count = 1.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 0, current count =
1. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count =
1. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count =
1. Changed database context to 'master

In the end I simply did what the server wanted, i.e. created all the "publication" tables (dbo.MSpeer_ and dbo.sys) in the problematic database (the one that was the publisher), through Script Table As -> CREATE To dialog. Thanks to this server allowed me to disable publishing and distribution. Then I only needed to reconfigure replication.

Hope, next time it crashes (actually I hope it won't), we'll be wise enough to backup tables involved in replication thoroughly.

Additional information by Zane, originally left in a comment:

The tables dbo.MSpeer_ and dbo.sys can also be created by configuring the distributor/publisher on a different server with SQL Server installed. This will create these tables in the database that you've set for publishing. See -> Tables - >System Tables in SSMS.

Then, generate the schemas for all these tables and then create these tables in the problematic database which will allow you to disable distribution and publishing and reconfigure the distributor.

StackExchange Database Administrators Q#30543, answer score: 4