SQL Server Move Database Files Step By Step

Problem

SQL Server Database Administrators often face the task of moving user database files to a new location. The reasons for these changes can be separating the location of the log and data files, moving files to a new disk due to a shortage of storage, distributing files between several disks in order to improve performance, and so on.

In this tip, we describe moving SQL Server user database’s files to a new location within the same instance.

Solution

Let’s assume we have a SQL Server database and want to move its data and log files to a new location. The database will remain on the same instance and nothing will be changed logically. Only the database files should have a new physical location.

The following example creates a sample database:

USE master 
GO
 
CREATE DATABASE TestDB 
GO
 
USE TestDB 
GO
 
CREATE TABLE TestTable 
( 
ID INT, 
Val CHAR (1) 
)
 
INSERT INTO TestTable(ID, Val) 
VALUES (1,'A'), (2,'B'),(3, 'C') 

As it is not mentioned in the database creation script, the database’s data and log files are created in the default location. We can see that by right-clicking on the database name in SQL Server Management Studio (SSMS) and choosing “Properties” then “Files” as shown below:

Obtain this information by running the following query:

SELECT name AS FileLogicalName, physical_name AS FileLocation
FROM sys.master_files 
WHERE database_id = DB_ID(N'TestDB') 

We can see the files logical names and physical locations:

Suppose we have specific folders for our data and log files (“C:\MSSQL\UserDBData” and “C:\MSSQL\UserDBLog” correspondingly). Now we want to move these files to the corresponding locations. This process should not affect other databases in the instance and all other databases should be available during this relocation. Let’s start the process.

Take Database Offline

First, we need to take the database offline. To do that, use the code below:

USE master
GO
 
ALTER DATABASE TestDB SET OFFLINE

After running this code, we can see that TestDB is now in the offline state:

Physically Move The Files

Then, we physically move the data and log files to new locations:

ALTER Database Properties to Point to Correct File Locations

The next step is to ALTER the database to use new paths of its files:

USE master
GO
 
ALTER DATABASE TestDB 
MODIFY FILE (NAME = TestDB, FILENAME = 'C:\MSSQL\UserDBData\TestDB.mdf')
 
ALTER DATABASE TestDB 
MODIFY FILE (NAME = TestDB_log, FILENAME = 'C:\MSSQL\UserDBLog\TestDB_log.ldf')

In the code above, “NAME” is the logical name of the file and “FILENAME” is the new path of the file. Do this for each database file that you want to relocate.

After executing the code, we can see the modification was successful. The new file path will now be used when the database is started.

Bring Database Back Online

Now, it is time to bring the database online. It is important to note the necessary permissions to new folders / files are required to bring the database online. SQL Server needs to be able to open the database files. If unable, the database cannot be brought online.

USE master
GO
 
ALTER DATABASE TestDB SET ONLINE

In SQL Server Management Studio, we can see that the database is online and the files’ paths are updated:

Conclusion

We have successfully moved the data and log files to the new location.

Next Steps

For more information, please follow the links below:

• Move Database Files
• Move User Databases
• sys.master_files

Sergey Gigoyan

Sergey Gigoyan (LinkedIn) is a Senior Technical Architect specializing in data and databases with more than 15 years of experience.  Sergey focuses on modern data architectures, database design and development, performance tuning and optimization, high availability solutions, BI development and DW design. He has worked with SQL Server, Oracle, and PostgreSQL databases, as well as cloud-based data solutions (AWS and Azure).  Sergey also has extensive experience with modern data stacks such as Snowflake and dbt.

Sergey’s experience spans various industries.  He had the privilege of working with IT giants such as Oracle as a Principal Data Engineer and BlackBerry as well as innovative startups.  He helped deliver complex database solutions and advanced data strategies.

Sergey is also the author of “Building a Successful Career in IT – How I Did It” where he provides actionable advice on thriving in the ever-evolving IT industry.

• MSSQLTips Awards: Champion (100+ tips) – 2024 | Author of the Year – 2020