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'. (Microsoft SQL Server, Error: 208)
It seems like some info was not recovered from the system database backups (probably distribution database may be missing something).
Also I tried to follow the answer given in unable to disable publishing and distribution. But to no success. I tried to create syspublications both in master and distribution, but nothing changed.
So, how can I make replication work on this server?
Tried to publish some other database on the server and it worked. So the problem seems to be connected with the published database. How can I clear it up to manage create a new publication over it?
System Tables subfolder of the problem database misses the replication tables, which are created, when a database is published.
In replication monitor I can see those old subscriptions, while they are not available in Replication folder.
Best Answer
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.