How to Move a Database That Has Snapshot Replication 1

[JohnBates]

hi everyone,

Hopefully someone (like me) who doesn't know Saturday from Tuesday will chip in with a tip.

This is SQL server 2000 Standard.

I need to move 1 user database to another drive on the same server.

Want to keep the same name for the db - just move it from C to E.

The problem is that it is configured for Snapshot replication.

??Do I really have to remove all the replication for this db to be able to detach it?

?? And will SQL Server allow the db on the E drive to have the same name as the (detached, soon-to-be-deleted) db on C ?

Just need to move it to a drive that has more available space.
Thanks!
John

 

[mrdenny]

Do I really have to remove all the replication for this db to be able to detach it?

Yes, you do need to remove all the replication from the db before you can detach it.

And will SQL Server allow the db on the E drive to have the same name as the (detached, soon-to-be-deleted) db on C ?

If the database has been detached then SQL doesn't know anything about it.

To do this you'll need to remove the replication, detach the database, move the files from the c drive to the e drive, resetup replication.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[Catadmin]

FYI, all you really have to do is remove the subscriptions for this DB. You don't have to delete the publications. Just drop the subscriptions, detach your DB, move the .MDF and .LDF (and any other) files to your new drive. Re-attach to SQL Server, then re-create your subscriptions.

Replication is a pain. It doesn't allow you to do a lot of things while it's in force. Fortunately, most of the time you only have to disable parts of it to do what you want instead of taking the whole thing down and recreating it from scratch.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[JohnBates]

Thanks Catadmin and mrdenny.

Replication is a pain. - Amen to that.

This is what I tried and, to my surprise, it worked beautifully:

1. Performed a full backup of the db
2. Took the db off-line
3. Restored the backup - pointing the files to the new E drive path, with 'Leave Database Operational' and 'No additional restores needed' checked
(Did all this thru E.Mgr)

When the Restore completed:
- the database was automatically on-line and pointing to the
data and log files on the E drive
- and the data and log files had been removed from the C drive where the db was originally
- I ran the Snapshot and Distribution jobs and they worked perfectly. Didn't even have to re-create the Subscription.

I didn't have to do anything to get the snapshot replication working. I was pleasantly shocked.

Maybe Merge or Transactional replication would require you to at least drop the Subscription OR maybe it worked because I didn't use the Detach/Reattach method, don't know.

I may add a FAQ for this one to help someone else.

John

 

[Catadmin]

Please do add a FAQ, because it is indeed different from Transactional (which is the one I use). Transactional requires a persistant connection to the DB, so it won't let you detach or do anything with a DB until replication is disabled.

Bleargh....

I guess since Snapshot doesn't require a persistant connection, that you can get away with a lot more than you can with Transactional.

Thanks for the FYI, BTW.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???