How To Move SQL Server Data Files

There always seems to be a need to move database files, whether it's a new system I'm setting up or an existing system that needs adjustments, or perhaps a critical emergency when the database stops working because a drive ran out of space.

When it comes to scripts, I like to write one query, see what I need and then execute it from that query (call me lazy...). To move a database file, whether it be tempdb or your primary production database, you need two main things. The logical name and the physical path. Here is a simple query that shows you both and gives you a script at the same time to execute to adjust it.

USE tempdb;
SELECT
    name,
    physical_name,
    'use master; ALTER DATABASE ' + DB_NAME() + ' MODIFY FILE (NAME = ' + name + ', FILENAME = ''' + physical_name + ''' );' AS ChangeScript
FROM
    sys.database_files;

Once you execute your query (don't forget to change your database name), you should see something like this below.

Once you have executed it, highlight the column 'ChangeScript' and copy it to paste into a new SSMS or Azure Data Studio window.

This script below is the output of the 'ChangeScript' column for my above query.

use master; ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'C:\Files\Databases\2019rc1\tempdb.mdf' );

use master; ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'C:\Files\DatabaseLogs\2019rc1\templog.ldf' );

use master; ALTER DATABASE tempdb MODIFY FILE (NAME = temp2, FILENAME = 'C:\Files\Databases\2019rc1\tempdb_mssql_2.ndf' );

use master; ALTER DATABASE tempdb MODIFY FILE (NAME = temp3, FILENAME = 'C:\Files\Databases\2019rc1\tempdb_mssql_3.ndf' );

use master; ALTER DATABASE tempdb MODIFY FILE (NAME = temp4, FILENAME = 'C:\Files\Databases\2019rc1\tempdb_mssql_4.ndf' );

use master; ALTER DATABASE tempdb MODIFY FILE (NAME = temp5, FILENAME = 'C:\Files\Databases\2019rc1\tempdb_mssql_5.ndf' );

use master; ALTER DATABASE tempdb MODIFY FILE (NAME = temp6, FILENAME = 'C:\Files\Databases\2019rc1\tempdb_mssql_6.ndf' );

use master; ALTER DATABASE tempdb MODIFY FILE (NAME = temp7, FILENAME = 'C:\Files\Databases\2019rc1\tempdb_mssql_7.ndf' );

use master; ALTER DATABASE tempdb MODIFY FILE (NAME = temp8, FILENAME = 'C:\Files\Databases\2019rc1\tempdb_mssql_8.ndf' );

Once you have your text in a new window, go ahead and adjust the path as needed. In this case, I'm moving my files from the folder 2019rc1 up back one folder.

After you edit, go ahead and execute your query to move the files.

Here is what you should see after executing:

Now the files have been moved you will need to stop SQL Server, and move the files to the new location you specified. Don't forget your log file. If the files are not moved you will see something like this below:

If you see this, make sure all files are moved, and then restart SQL Server.

If you are moving tempdb once you adjust it and restart SQL Server, it will move the files for you.

How To Move SQL Server Data Files

Recent Posts

Comments