How to move SQL database files (MDF and LDF) to another location

Pre-requisites

In case a database is being used by any Windows services or other resources, these must be stopped in order to allow altering SQL database files. Also, any existing connections to a database must be closed. Before the first step, make sure to locate the appropriate MDF and LDF files for a database you want to work with. By default, these names are in the following format:

• Database_name_Data.mdf – for MDF file
• Database_name_log.ldf – for LDF file

The above mentioned format does not need to be necessarily used, so make sure you are targeting correct files.

Moving database files to another location

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

ALTER DATABASE AdventureWorks2014  

    MODIFY FILE ( NAME = AdventureWorks2014_Data,  

                  FILENAME = 'E:\New_location\AdventureWorks2014_Data.mdf');  

GO

 

ALTER DATABASE AdventureWorks2014  

    MODIFY FILE ( NAME = AdventureWorks2014_Log,  

                  FILENAME = 'E:\New_location\AdventureWorks2014_Log.ldf');  

GO

The New_location is a folder created on a separate drive (in this specific case, we will change from a default C to E drive on a local machine) with sufficient disk space for SQL database files. Specified folder must be created first, in order to be used as a new location for SQL database files in the above SQL statement

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

ALTER DATABASE AdventureWorks2014 SET OFFLINE;  

GO

This is important in order to perform the next step. If a database is being used by any application, this step cannot be accomplished, unless all connections to a database are closed.

• 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.

Important note: Make sure that SQL Server can access the specified location. Otherwise, the following error will appears:

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.)”.

To fix this:

• Start SQL Server Configuration Manager
• Right click a SQL Server instance that hosts a database which files are moved to a new location and choose the Properties option from the drop-down list:

Instead of the current account, switch to the one that has access to a drive where files are moved:

• Once this is done, a 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:

Once this is done, a SQL database will be hosted on a drive with sufficient free space and the user can continue using it.

 

From <https://www.sqlshack.com/move-sql-database-files-mdf-ldf-another-location/>

 

 

Created with OneNote.