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
