SQL Server Move Database Files Step By Step

Table of Contents

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 are going to describe the process of moving a 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 we want to move its data and log files to a new location. Additionally, the database should remain in the same instance and nothing should 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:

sql server move database 1

This information can be obtained 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:

sql server move database 2

Suppose we have