Move SQL Server database files

MoveSQLFiles.md

Move SQL Server database files

1- Set the database offline

ALTER DATABASE DatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE;

2- Now you can move the files manualy

3- Set the new files location

ALTER DATABASE DatabaseName MODIFY FILE ( NAME = DatabaseName, FILENAME = 'D:\Database\DatabaseName.mdf' );
ALTER DATABASE DatabaseName MODIFY FILE ( NAME = DatabaseName_log, FILENAME = 'D:\Database\DatabaseName_log.ldf' );

4- Set the database online

ALTER DATABASE DatabaseName SET ONLINE;

You can validate the files location with the following query

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