April 11, 2006 at 1:51 pm
I want to move the log file for a database that is a publisher & a subscriber. A DB can't be detached if it's being used for replication. I could script out everything and delete the publications. I think the DB will still show it's used for replication, so I have to find that SP to "unmark" it as being used for replication, then move the log file, and set replication all back up again.
Does anyone have any thoughts on a better way to accomplish this ?
April 12, 2006 at 6:17 am
As you said, you'll need to drop the publication before being able to detach the database. Yes, script the publication/subscription then drop it. Move the log file, then bring the replication back from the scripts.
April 12, 2006 at 7:12 am
Try this...
The only thing is that you need to attach to the same server...
exec sp_configure 'allow updates', 1
reconfigure with override
go
update sysdatabases set category=0 where name='your db name here'
go
--detach here
--attach here
update sysdatabases set category=1 where name='your db name here'
go
exec sp_configure 'allow updates', 0
reconfigure with override
go
April 19, 2006 at 4:04 am
This is something I've tried before that works. It's quick and easy and doesn't involve modifying any system tables. All you do is stop all replication processes, back up the database, then restore over it using the WITH MOVE option, specifying the new log file location.
John
April 20, 2006 at 3:46 pm
I have used that method too. Can vouch for the Restore WITH MOVE option after Repl is down.
April 20, 2006 at 6:57 pm
I can vouch as well. The key is to make sure you don't have active users in it beforehand. Replication happens to be an active user so you just stop it (not remove) until you are done with the restore.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply