April 1, 2008 at 8:28 am
So I somewhat unknowingly removed the system database distribution manually and I am unable to use the Disable Publishing and Distribution Wizard to clear all replication objects.
I am getting the error 'Could not locate entry in sysdatabases for database 'distribution' (SQL Server Error: 911).
Is there any way to fix this? I just want to set up all of my Replication items from scratch again. I am not sure how to clear out this remnant of a database reference.
April 1, 2008 at 8:54 am
I'd be curious to know how you did that, mostly to tell people not to do that.
Since system table updates are disabled in 2005, I'm not sure what you can do. Are the MDF/LDF still around? Can you detach/attach them? Or can you sp_addserver with the distribution db and same paths?
August 28, 2008 at 4:26 am
It can be done in the following way;
1. Take one fresh, blank install of SQL 2005
2. Add one idiot to the mix (Yours truly:D)
3. Configure Distribution, creating a nice working sys distribution db.
4. Attempt to restore a distribution db backup using documented methods.
5. Restore Over Existing DB, new distribution db ends up a user db.
6. Delete "User" db named "distribution"
7. Enter a whole new world of SQL Pain.
August 28, 2008 at 12:12 pm
Remove the replication "programmatically" by calling the system stored procedures directly. This in outlined in BOL:
Stop all replication-related jobs. For a list of job names, see the "Agent Security Under SQL Server Agent" section of Replication Agent Security Model.
At each Subscriber on the subscription database, execute sp_removedbreplication to remove replication objects from the database. This stored procedure will not remove replication jobs at the Distributor.
At the Publisher on the publication database, execute sp_removedbreplication to remove replication objects from the database.
If the Publisher uses a remote Distributor, execute sp_dropdistributor.
At the Distributor, execute sp_dropdistpublisher. This stored procedure should be run once for each Publisher registered at the Distributor.
At the Distributor, execute sp_dropdistributiondb to delete the distribution database. This stored procedure should be run once for each distribution database at the Distributor. This also removes any Queue Reader Agent jobs associated with the distribution database.
At the Distributor, execute sp_dropdistributor to remove the Distributor designation from the server.
Note:
If all replication publishing and distribution objects are not dropped before you execute sp_dropdistpublisher and sp_dropdistributor, these procedures will return an error. To drop all replication-related objects when a Publisher or Distributor is dropped, the @no_checks parameter must be set to 1. If a Publisher or Distributor is offline or unreachable, the @ignore_distributor parameter can be set to 1 so that they can be dropped; however, any publishing and distributing objects left behind must be removed manually.
August 28, 2008 at 12:26 pm
John.D.Wilson (8/28/2008)
It can be done in the following way;1. Take one fresh, blank install of SQL 2005
2. Add one idiot to the mix (Yours truly:D)
3. Configure Distribution, creating a nice working sys distribution db.
4. Attempt to restore a distribution db backup using documented methods.
5. Restore Over Existing DB, new distribution db ends up a user db.
6. Delete "User" db named "distribution"
7. Enter a whole new world of SQL Pain.
L O L
* Noel
June 18, 2010 at 1:33 am
John.D.Wilson (8/28/2008)
It can be done in the following way;1. Take one fresh, blank install of SQL 2005
2. Add one idiot to the mix (Yours truly:D)
3. Configure Distribution, creating a nice working sys distribution db.
4. Attempt to restore a distribution db backup using documented methods.
5. Restore Over Existing DB, new distribution db ends up a user db.
6. Delete "User" db named "distribution"
7. Enter a whole new world of SQL Pain.
Yes, that's how you do it. I have tested it. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply