Moving Database Files and Replication

Today’s post is a quick one as I am a bit under the cosh.

There are many ways to move database files, detach and attach, backup and restore, alter database.  But what can you do if the files you want to move belong to a database being used in replication?  Here is one solution I used recently;

USE master

GO

-- set database to restricted user mode

ALTER DATABASE databasename SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

GO

-- set database offline

ALTER DATABASE databasename SET OFFLINE;

GO

-- modify database files

ALTER DATABASE databasename

MODIFY FILE ( NAME = logicalfilename, FILENAME = 'new file path' );

ALTER DATABASE databasename

MODIFY FILE ( NAME = logicalfilename, FILENAME = 'new file path' );

GO

-- move the files to the new drive / directory

-- set the database online

ALTER DATABASE databasename SET ONLINE;

GO

-- set the database to multi user

ALTER DATABASE databasename SET MULTI_USER;

GO

As always there are the usual precautions to take - never do this on a production system without testing it first!!!

Enjoy!

Chris