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.
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
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
Run the following SQL script to take a SQL database offline:
ALTER DATABASE AdventureWorks2014 SET OFFLINE; GO
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
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
This should give the following result:
Leave a Reply