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

TheFirst up, is the TempDb databasedatabase.
It is recreated on service startup.
So,startup, and is simplest to move.

NOTE: This means, you candon't simplyhave updateto itsactually path,move files around for this.
You only have to change the assigned file path and restart SQLthe Server.service.

DoNOTE: thisYour SQL engine may be configured with more than one tempdb, to updatereduce 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 it: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 thatthe isconfigured done,tempdb locations are updated, restart the SQL engine service, for the change to take effect.

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

USE master;
GO
SELECT
    name,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.

 

Back to top