Sometimes we need to move database files from one location to another to free up some space or to optimize the layout. The KB is describing how to do that correctly.

Step-by-step guide

1. If you are moving CT database, disable the Tesla job which uses it.
   If you are moving a regular database, reach out to the owners and arrange a maintenance window.

2. Get the current database files Logical Name and Physical Location

USE master

GO

SELECT name AS LogicalFileName, physical_name AS FileLocation, state_desc AS Status

FROM sys.master_files

WHERE database_id = DB_ID('<Database_Name>');

3. 
   Take the Database offline.
   At this step there may be blocking, so you need to resolve it (be sure that Tesla jobs are disabled or reach out to the DB owners if they have forgotten to disable their applications).

USE master

GO

ALTER DATABASE <Database_Name> SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

4. Copy the database files physically to the new location. IMPORTANT: use "COPY+PASTE" approach, NOT "CUT+PASTE".

5. Use ALTER DATABASE to modify the FILENAME to new location for every file moved

USE master

GO

ALTER DATABASE <Database_Name>

MODIFY FILE

( NAME = Testdb_Data, -- Datafile name

FILENAME = 'C:\Disk2\Testdb_Data.mdf'); -- New file path

USE master

GO

ALTER DATABASE <Database_Name>

MODIFY FILE

( NAME = Testdb_Log,-- Logfile name

FILENAME = 'C:\Disk2\Testdb_log.ldf'); -- New file path

6. 
   Set the database ONLINE:

USE master

GO

ALTER DATABASE <Database_Name> SET ONLINE;

7. 
   Now, verify the database files Physical location:

USE master

GO

SELECT name AS FileName, physical_name AS CurrentFileLocation, state_desc AS Status

FROM sys.master_files

WHERE

database_id = DB_ID('<Database_Name>');