Move SQL Server database files to another location

Spread the love


If any drive is full due to the large database data file (LDF or MDF) size! How to moving MS SQL Server Database files (MDF, LDF & NDF) to another location step by step.


Need to move database data file to another location/drive.


New location

The New_location is a folder, needs to be created on a separate drive with sufficient disk space for SQL database files. The specified folder must be created first, in order to be used as a new location for SQL database files in the below SQL statement. Make sure that SQL Server can access the specified location.

Set a new location

Run the following SQL script to set a new location for SQL database files:

Database offline

Run the following SQL script to take a SQL database offline:

Or using SSMS we can also make the database offline

Move MDF and LDF files

Move MDF and LDF files of the specific SQL database to a new location specified in the statement above. This means to simply cut mentioned files from the existing location and to move them to a newly specified one. #Make sure that SQL Server can access the specified location. Otherwise, the following error will appear:

Msg 5120, Level 16, State 101, Line 13

Unable to open the physical file “E:\New_location\AdventureWorks2014_Data.mdf”. Operating system error 5: “5(Access is denied.)”.

Make database online

Once the above steps complete successfully, the database can be set online by running the following query to get back a database online:


To verify that the process is finished successfully run the following query:

This should give the following result:

Spread the love
Author: OTi
Meet OTi, the brains behind the popular tech blog "" OTi is an accomplished author, tech enthusiast, and an expert in SQL Server. With over a decade of experience in the tech industry, he has become a trusted voice in the world of database management. OTi's passion for technology began at a young age, and he has been tinkering with computers ever since. He graduated with a degree in Computer Science and has since worked for some of the biggest tech companies in the world. His experience has given him a deep understanding of how technology works and how it can be used to improve our lives.

Leave a Reply

Your email address will not be published. Required fields are marked *