Move SQL Server database files to another location

405 views 05:45 0 Comments 20 September 2019

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.

Steps

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:

ALTER DATABASE Database_Name   --[Example: AdventureWorks2014]
MODIFY FILE ( NAME = Logical_file_name,   --[Example: AdventureWorks2014_Data]
FILENAME = 'E:\New_location\file_name.mdf');  --[Example: E:\New_location\AdventureWorks2014_Data.mdf]
GO
SQL
ALTER DATABASE Database_Name --[Example: AdventureWorks2014]
MODIFY FILE ( NAME = Logical_file_name, --[Example: AdventureWorks2014_Log]
FILENAME = 'E:\New_location\file_name.ldf'); --[Example: E:\New_location\AdventureWorks2014_Log.ldf]
GO
SQL

Database offline

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

ALTER DATABASE AdventureWorks2014 SET OFFLINE;
GO
SQL

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:

ALTER DATABASE AdventureWorks2014 SET ONLINE;  
GO
SQL

Verify

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

SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks2014')
GO
SQL

Leave a Reply

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