How often you got to the point that for any reason you don’t have enough space on the specific drive to host a database? In case of database development or other tasks outside the production environment, this should not be a problem as a database can be eventually re-created, restored from a backup and set to be hosted on another location. By default, SQL Server stores database files in its installation folder, specifically in the Data folder:

However, what if a SQL database is a production one or it is being constantly used by the specific application? This means that when it comes to the point of a low disk space, there should be a way to move database files (MDF and LDF) to another drive (with sufficient disk space) while the actual database will still be hosted by the same SQL Server instance. Let’s see how to move database files to another location. We’ll use a sample AdventureWorks database in this case.

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:
  1 2 3 4 5 6 7 8 9

  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:
  1 2	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:
  1 2	ALTER DATABASE AdventureWorks2014 SET ONLINE;   GO

• To verify that the process is finished successfully run the following query:
  1 2 3 4

  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.

• Author
• Recent Posts

Marko Radakovic

Marko is an IT and technical education teacher, who likes movies, video games, and heavy metal music.

He uses his spare time to play guitar, ride a bike and hang out with his friends. During winter, he likes skiing the most, but all other snow activities, too.

He is also author of various SQL Shack articles about SSIS packages and knowledgebase articles about ApexSQL Doc.

View all posts by Marko Radakovic

Latest posts by Marko Radakovic (see all)

• How to move SQL database files (MDF and LDF) to another location - January 22, 2018
• Understanding SQL Server database static data and how it fits into Database lifecycle management - January 13, 2017
• Revision history of an object change in a SQL database using Mercurial - June 30, 2016