Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Comments

Leave a comment on the original post [sqlserver365.blogspot.com, opens in a new window]

Loading comments...