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