How to move database files in SQL Server - IT blog site for Cloud, Containers and Databases

Detailed steps on how to move database files from one location to another. We will see how to move user database files, and how to move system database files. We will see how to move both mdf and ldf files to another location.

Move User Database Files

First find the database files location which we want to move. To do that, execute the following query.

use master
go
select db_name(database_id) 'DatabaseName', name 'LogicalFileName',physical_name 'PhysicalFileName'
from sys.master_files 
where database_id = db_id('DatabaseName') --Replace DatabaseName with your database name

2. Now that you have the logical and physical file names, navigate to the new folder or drive where you have to move the files. That is., the destination folder or directory. Right click on the destination folder and give full access to the SQL Server service account.

3. Now update the new file locations in master database using the below query. This will update the new database files location for the specified database in the master database.

use master --Make sure to update DatabaseName, logicalfilename(name) nad physicalfilename(filename)
go
alter database DatabaseName 
modify file (name = DatabaseName_Data, filename = 'F:\MSSQL\NewLocation\DatabaseName_Data_1.mdf');
go
alter database DatabaseName
modify file (name = DatabaseName_Log, filename = 'L:\MSSQL\NewLocation\DatabaseName_Log.ldf');
go

3. Now we are ready to move the files physically. First we have to bring the database offline using the below query. Note that the clause with rollback immediate will kill all sessions and bring the database offline. Please be careful to use that. I would suggest you stop the application connections and inform end users to close their connections, rather than killing or rolling back all transactions.

alter database DatabaseName set offline; --Replace DatabaseName with your database name
--Or Use
alter database DatabaseName set offline with rollback immediate; --Use this only if you are sure what you are doing

4. Now that the database is offline go ahead and move the files physically from old location to new location. Once the files are moved, run the below query to bring the database offline and verify the new path.

use mater
go
alter database DatabaseName set online
go
select db_name(database_id) 'DatabaseName', name 'LogicalFileName',physical_name 'PhysicalFileName'
from sys.master_files
where database_id = db_id('DatabaseName') --Repalce DatabaseName with your database name

Click on the below links to learn how to Move System Database Files

Share this:

4 thoughts on “How to move database files in SQL Server”