September 28, 2009 at 3:12 pm
Okay here's what I did to screw up my day.
1. I had to move file to a new drive but had no way to move the distibutor.ldf and mdf, so I left them for the time being.
2. The drives where unmounted this morning.
3. I ran a hard drop on the distibutor DB and it removed it from the system databases.
4. I had the drives remounted and copied the ldf and mdf over and now I need to attach the distributor database back to the system tables.
I tried
sp_removedbreplication
deleted all jobs
(this was an attempt to remove replication and start from scratch but it still think the distibutor is there)
then I tried
-c -m- -t3608
stop and started services
use master
go
sp_attach_db 'distribution','G:\sqldata\distribution.MDF','H:\Sqllog\distribution.LDF'
go
removed -c -m- -t3608
use distribution
go
sp_helpfile
go
and it still attached to user databases.
HELP!!!!!!!!!!!!!!!!!!!!!!!
MCSE SQL Server 2012\2014\2016
September 29, 2009 at 8:20 am
Okay I got it back online and this is what I did.
1. sp_get_distributor
2. delete from msdb.dbo.MSdistpublishers
delete from msdb.dbo.MSdistributiondbs
3. sp_adddistributiondb 'distribution'
4. Created a new publication and enabled it
5. created a subscription to another server
It works!
btw I don't recommend anybody do what I did, always use these steps to properly remove your replication when moving files or even renaming a sever. This is for Push Trans. Go to http://support.microsoft.com/kb/324401 for information on pull or snapshot replication in order to remove those.
USE <Publication database name>
GO
EXEC sp_dropsubscription @publication = N'Publication Name', @article = N'all', @subscriber = N'all', @destination_db = N'all'
USE <Publication database name>
GO
EXEC sp_droppublication @publication = N'Publication Name'
USE master
GO
EXEC sp_replicationdboption @dbname = N'Publication database name', @optname = N'publish', @value = N'false'
USE master
GO
EXEC sp_dropsubscriber @subscriber = N'Subcription server name', @reserved = N'drop_subscriptions'
USE master
GO
EXEC sp_dropdistributor @no_checks = 1
sp_removedbreplication 'Publication database name'
MCSE SQL Server 2012\2014\2016
September 29, 2009 at 8:26 am
thanks for the update. The other thing you can try is to script out replication and then re-run the scripts to set things up.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply