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.
