Skip to main content

Moving Databases to New Volume

Here are steps to move databases from one volume to another.

NOTE: These steps assume that you can take the SQL engine offline as needed for this activity.

Create New File Paths

To see the existing configured paths, open SSMS and connect to the SQL engine host with sys privileges.
Once open, right-click the SQL engine and select properties.

image.png

Then, select Database Settings.

image.png

The above screenshot shows the three file paths that SQL Server uses for data:

  • Data (databases)
  • Log (transaction logs)
  • Backup (where it pushes backups)

Normally, the transaction log files, alongside the database files.
So, the first two paths will usually be the same.

The third is an optional, default path, where backups are pushed.

NOTE: Be sure to create the new base path in the local filesystem, and not on a distributed or network share.
The SQL Engine service utilizes VSS and share locks. Neither of which, work well on a distributed or network share.

Once you've decided on a path, make sure the folder security allows the SQL engine service to have full privileges (read/write/delete).

NOTE: Be sure to set folder security, of the target base path, so that the SQL engine service can access files.
This usually causes trouble when the SQL engine host is restarted after a database file move.

Moving TempDb

First up, is the TempDb database.
It is recreated on service startup, and is simplest to move.

NOTE: This means, you don't have to actually move files around for this.
You only have to change the assigned file path and restart the service.

NOTE: Your SQL engine may be configured with more than one tempdb, to reduce allocation contention.

Normally, the following query will show your tempdb file locations.

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

NOTE: the above query will not return the paths for ALL tempdb database.

To be sure that you see the location of ALL tempdb files, use this more advanced query:

USE master;
GO
SELECT
    name        AS LogicalName,
    type_desc   AS FileType,
    physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
ORDER BY type_desc, name;

You will see something like this:

image.png

NOTE: The above shows our normal tempdb file (and its log file), as well as, a second tempdb called: temp2.
This means that the SQL engine host has been configured with a second tempdb database to help with queries.

When ready to move the tempdb files, execute this:

NOTE: Be sure to include an alter statement for each tempdb that you intend to move.
This requires you to name each one, like the following example does.

USE master;
GO
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = N'F:\MSSQL\DATA\tempdb.mdf' );
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = N'F:\MSSQL\DATA\templog.ldf' );
ALTER DATABASE tempdb MODIFY FILE ( NAME = temp2, FILENAME = N'F:\MSSQL\DATA\tempdb_mssql_2.ndf' );

Once the configured tempdb locations are updated, restart the SQL engine service, for the change to take effect.

After the SQL engine service is restarted, you can verify the tempDb has moved with this:

USE master;
GO
SELECT
    name        AS LogicalName,
    type_desc   AS FileType,
    physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
ORDER BY type_desc, name;

Moving Model and msdb

These are the master databases for the SQL engine host.

Here are steps to move them.

NOTE: They require a service restart to be moved.

Get their current paths with this:

USE master;
GO
SELECT DB_NAME(database_id) AS DbName, name, physical_name
FROM sys.master_files
WHERE DB_NAME(database_id) IN ('model','msdb');

You'll see something like this:

image.png

Now, is a good time to identify their folder with a File Explorer window.

Update their paths to the target location, with this:

USE master;
GO
ALTER DATABASE model MODIFY FILE ( NAME = modeldev,  FILENAME = N'F:\MSSQL\DATA\model.mdf' );
ALTER DATABASE model MODIFY FILE ( NAME = modellog,  FILENAME = N'F:\MSSQL\DATA\modellog.ldf' );

ALTER DATABASE msdb  MODIFY FILE ( NAME = MSDBData,  FILENAME = N'F:\MSSQL\DATA\MSDBData.mdf' );
ALTER DATABASE msdb  MODIFY FILE ( NAME = MSDBLog,   FILENAME = N'F:\MSSQL\DATA\MSDBLog.ldf' );

Once the paths are configured, you need to stop the SQL engine service.

DO NOT RESTART THE SQL ENGINE SERVICE UNTIL YOU'VE MOVE THE FILES!

NOTE: When moving database files, make sure moves are done as a file MOVE and not as a file COPY.
A file copy, within the same volume, will create a second copy of the file, and consume extra space.
Whereas, a file MOVE will simply update file pointers for the new path, and not actually affect content.

With File Explorer, move the database and log files for model and msdb, to their new location.

Once the files are moved, you can restart the SQL engine service, and they will be mounted at their new location.

Extra Model Database Files

After moving the configured model and msdb files, in the previous section, check the source file path for any remaining model files.
These would be named like the following:

image.png

Normally, you won't see these, but they could be leftovers from a replication or upgrade.

To ensure that they are NOT attached to any database on the SQL engine host, verify they don't show up in the following query:

USE master;
GO
SELECT DB_NAME(database_id) AS DbName, name, physical_name
FROM sys.master_files
WHERE physical_name LIKE N'%model_%'
   OR physical_name LIKE N'%replicatedmaster%'
   OR physical_name LIKE N'%msdb%'
ORDER BY physical_name;

You should see just the model and msdb files (with their logs):

image.png

And for a sanity check, verify that only the master database is included in the service startup, with this:

USE master;
GO
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('master');

You should see only the master database and its log file:

image.png

NOTE: Extra model files, like this, could be from a replication or upgrade.
But, do check that there is NOT another SQL engine running on the host, and is sharing the sql folder.
This can happen on development machines.

As one last check, you can ask SQL to give a list of all database files it thinks it is using, with this:

USE master;
GO
SELECT
    d.name AS DbName,
    mf.type_desc,
    mf.name AS LogicalName,
    mf.physical_name
FROM sys.databases d
JOIN sys.master_files mf ON mf.database_id = d.database_id
ORDER BY d.name, mf.type_desc, mf.name;

The above query should list every database that SQL is using; master, model, msdb, and all user databases.

Once you've confirmed that the extra model files, in the source file path are not listed in the above queries, then there is not need to carry them forward to the new file path.
You can safely delete them.

 

 

Back to top