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 :
|
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:
|
1 |
ALTER DATABASE DB_NAME MODIFY FILE ( NAME = 'DATAFILE_NAME', FILENAME = 'path\datafile.mdf' ); |
4) Bring database online
|
1 |
ALTER DATABASE DB_NAME SET ONLINE; |
At anytime, you can check status of your database datafiles:
|
1 2 3 |
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'DB_NAME'); |
