Moving SQL Server database files to another location in SQL Server

Issue

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.

Resolution

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

Deployment Steps

New location

The New_location is a folder, need 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:

Verify

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

This should give the following result:


Comments

Leave a Reply

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