Move SQL Server 2008 datafile to another location

In order to reallocate datafiles to other locations, for instance in case you need to move mdf’s to a faster HDD, you must take following steps:

1) Take affeccted DB offline :

ALTER DATABASE DB_NAME SET OFFLINE WITH ROLLBACK IMMEDIATE;

1	ALTER DATABASE DB_NAME SET OFFLINE WITH ROLLBACK IMMEDIATE;

2) Physically move ldf or mdf files to new location

3) Execute, for each datafile:

ALTER DATABASE DB_NAME MODIFY FILE ( NAME = 'DATAFILE_NAME', FILENAME = 'path\datafile.mdf' );

1	ALTER DATABASE DB_NAME MODIFY FILE ( NAME = 'DATAFILE_NAME', FILENAME = 'path\datafile.mdf' );

4) Bring database online

ALTER DATABASE DB_NAME SET ONLINE;

1	ALTER DATABASE DB_NAME SET ONLINE;

At anytime, you can check status of your database datafiles:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'DB_NAME');

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'DB_NAME');

No comments yet.