Change Microsoft SQL Server Data Location

Let’s say you have an existing Microsoft SQL data and want to move it to a
different disk or directory. Running the query below shows the logical name and the file location of your schema.

SELECT name AS FileLogicalName, physical_name AS FileLocation FROM sys.master_files
WHERE database_id = DB_ID('HR');  

Existing Location

Target New Location

Steps to move the database files

1. Take the database offline

   ALTER DATABASE HR SET OFFLINE
   

2. Moves the files manually

   • Navigate to C:\MSSQL_DATA\OldPath\
   • Move HR.mdf and HR.ldf to the new location

3. Update SQL Server with the new file paths

    ALTER DATABASE HR
    MODIFY FILE (Name = HR, FILENAME = 'D:\MSSQL_DATA\MSSQL15.MSSQLSERVER\MSSQL\Data\HR.mdf')
   
    ALTER DATABASE HR
    MODIFY FILE (Name = HR_log, FILENAME = 'D:\MSSQL_DATA\MSSQL15.MSSQLSERVER\MSSQL\Data\HR.0.ldf')
   

4. Bring the database back online

   ALTER DATABASE HR SET ONLINE;