Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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.

share|improve this answer
1  
This answer helped me too and the scenario was same as above. I'd like to add some points that the tables dbo.MSpeer_* and dbo.sys* can be created configuring distributor/publisher in different server with sql server installed which will create these tables under Database(which you've set for publishing) -> Tables - >System Tables. Then, generate the schemas for all these tables and then create these tables in the problematic database which shall allow you to disable distribution and publishing and reconfigure distributor. –  Zane Jul 17 at 3:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.