Moving Database Files SQL Server

Move Database Files to Different Location on Disks

On this part I'm going to show you how to move database files from one location to another location on the server disks. Bear in mind that this will need downtime for the database, so you need to make sure that there is no activity going on inside the SQL Server to the database. Also you need to make sure the new disk drives have enough spaces to store data files and log files with also in consideration of the files' growth.

1. List the Database Files

First before we are going to move the database files, list all of the data files and log files from the Database which will be moved. To see database files you can see from the properties of the database and you can also use query to list all the files and path.

Here is the query which you can use:

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

Note: change the "TestDB" name to the target database name

Save the result of this query so later we have all the files needed and the path to each file too

2. Take Database Offline

Next is, you must take the database offline, so here I used set offline with rollback immediate so to force every transaction that is still going to rollback and the database can be taken offline immediately. You might or might not need to use the with rollback immediate option but I always make sure there is no activity to database still going and use rollback immediate when execution

Use this query:

ALTER DATABASE TestDB SET OFFLINE WITH ROLLBACK IMMEDIATE 

3. Copy The .ldf and .mdf data files to The Destination path

Old Path:

New Path:

When the database has been taken offline, now you must copy (or cut) the data files and log files of the database (which had been listed on the beginning) to the new destination path on other disk drives. So in the example I moved TestDB database files from C: to D:

4. Set the new path to be the path where the database files are hosted

Now all you need to do is to Alter the Database in SQL Server to configure the database files mapped to the right new path which we had moved the data files and log files to new disk drive path. Here is the query to do this:

USE master
GO

ALTER DATABASE TestDB 
MODIFY FILE (NAME = TestDB, FILENAME = 'D:\TestDB\TestDB.mdf');

ALTER DATABASE TestDB 
MODIFY FILE (NAME = TestDB_log, FILENAME = 'D:\TestDB\TestDB_log.ldf');

Note: Change the NAME of the files and also the path according to your needs

5. Set Database Online

The last thing to do after mapping the configuration for database files is to set the database back online. You can use this query to set database online:

USE master
GO

ALTER DATABASE TestDB SET ONLINE

Note: change the database name according to your database.

After the database is online you can check again to see that the data files are now hosted on the new path. You can start working with your database again as usual.