Wednesday, May 29, 2013

Cannot drop the database because it is being used for replication.

In the event you want to move a database that is configured as a publisher for replication, a detach/attach will not work.  Rather, you can simply take the database offline, move the data file, adjust the properties and bring it back online.  I saw suggestions to backup/restore, but this is much quicker and more effective in my opinion.

Sample script:

ALTER DATABASE <database name> SET OFFLINE

-- move file to new location

ALTER DATABASE <database name> MODIFY FILE ( NAME = <logical file name>, FILENAME = '<path\file name>')

ALTER DATABASE <database name> SET ONLINE