A planned SQL Server file move has two coordinated parts: update the database catalog with the new paths, then take the database offline and move the physical files. Record every logical and physical name before starting.
Last updated: September 26, 2026.
SELECT name, type_desc, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N'Sales');
GO
ALTER DATABASE Sales
MODIFY FILE (NAME = N'Sales_Data', FILENAME = N'E:SQLDataSales.mdf');
ALTER DATABASE Sales
MODIFY FILE (NAME = N'Sales_Log', FILENAME = N'F:SQLLogsSales_log.ldf');The NAME values are logical file names from sys.master_files. Update every file, including secondary data files, before the offline step.
Perform the planned relocation
- Back up the database and test the restore path.
- Create target folders and grant the SQL Server service account access.
- Set the database offline during an approved maintenance window.
- Move—not copy and leave ambiguous—the files to the exact catalog paths.
- Bring the database online and verify every current location.
Use ALTER DATABASE Sales SET OFFLINE WITH ROLLBACK IMMEDIATE; only when you accept terminating active transactions. Otherwise, drain application connections first.
Avoid common startup failures
If a filename, drive, or permission is wrong, bringing the database online fails. Read the SQL Server error log, correct the filesystem or catalog path, and retry. Do not detach the database without a tested recovery plan; an offline move retains the database metadata on the instance.
Microsoft’s user-database relocation guide documents the planned and failure-recovery procedures. System databases require a separate process.
Verify after the move
Query sys.master_files again, bring the application online, check the error log, and monitor I/O latency and free space. Run a new backup so recovery documentation reflects the current layout. To create a separate copy instead, restore the database under another name with WITH MOVE.
