This is a topic which I encountered myself a few months back and it drove me mad for a while, but the solution is actually incredibly simple.
Normally it’s a simple task of “detach -> move file -> attach” and you’re done. But of course this doesn’t work with published databases as you get the error:
The only advice I could find on the internet was terrifying. People were adamant that I needed to script and remove replication, detach -> move files -> attach, and then re-create replication from the scripts, re-connect all subscribers, and re-initialise.
In this case I had 5 subscribers but 2 of the 5 were global (India and Eastern Europe), the network link is not fast, and the replication snapshot was 45GB!!! Basically even using remote snapshots (description found here), which I have to do, a rebuild takes around 15 hours and can only be done on a weekend when the line is quiet.
Therefore removing replication was not an option. Luckily I persevered and, through trial and error, found a VERY simple solution. Take the database offline.
Once offline you simply tell SQL you’re moving the files, physically move them, and then bring the database back online. That’s it. Replication carries on as if nothing has happened. No dramas, no problems.
Here’s a script (run it in 2 separate sections):
-----------------------------------------------------------------
-- Section 1 (Run whole section at once)
-----------------------------------------------------------------
-- First kill all connections to the database...
use master
go
declare @db varchar (20)
set @db = 'boredDBA'
declare @kill varchar(35)
declare crKILL cursor for
select distinct spid
from master.dbo.sysprocesses p
join master.dbo.sysdatabases d
on d.dbid = p.dbid
where d.name = @db
open crkill
fetch crkill into @kill
while @@fetch_status = 0
begin
set @kill = 'kill'+ ' ' + @kill
exec (@kill)
set @kill = ''
fetch crkill into @kill
end
close crkill
deallocate crkill
-- This next line takes the database offline...
alter database boredDBA set offline;
-----------------------------------------------------------------
-- Section 2
-----------------------------------------------------------------
-- Move the files at OS level
-- To find the files, use the following:
select *
from master.sys.master_files
where DB_NAME(database_id) = 'boredDBA'
-- Once moved at the OS level, tell SQL where to find them...
alter database boredDBA
modify file ( name = boredDBA_data, filename = 'd:\data\boreDBA_data.mdf' );
alter database boredDBA
modify file ( name = boredDBA_log, filename = 'l:\logs\boredDBA_log.ldf' );
-- Now bring the database back online
alter database boredDBA set online;