Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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

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
    set @kill = 'kill'+ ' ' + @kill
    exec (@kill)
    set @kill = ''
    fetch crkill into @kill

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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.