How to easily move a replicated database in SQL Server
Published: Apr 28, 2014
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;
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron