Move the Master Database to a New Location in SQL Server

To move the master database, change its startup parameters, stop SQL Server, move the files and start it again. Master is the one system database that ALTER DATABASE can’t move. The reason is how SQL Server finds it.

Why ALTER DATABASE Doesn’t Work for Master

Every other database stores its file paths in the master database. That’s how SQL Server finds them at startup. Master has nobody to ask. The service must open master first, so it reads the paths from its own startup parameters in the Windows registry. So ALTER DATABASE master MODIFY FILE can’t move the master database.

Read the Current Paths

Two queries show where master lives and what the startup parameters say. They don’t change anything.

SELECT name, physical_name, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'master');
SELECT value_name, CONVERT(nvarchar(260), value_data) AS value_data FROM sys.dm_server_registry WHERE value_name LIKE N'SQLArg%' ORDER BY value_name;

The three parameters are the ones to know. -d is the master data file. -l is its log file. -e is the error log. Move master, and you change -d and -l. Leave -e alone unless you also want a new error log folder.

Back Up Master First

A mistake in a startup parameter can stop the instance. Master holds the logins and the settings of the server. Take a backup before you touch anything, and check that it is readable. COPY_ONLY keeps this one-off backup out of your regular backup chain. A file name without a folder goes to the instance’s default backup folder. Run this block by hand. It writes a file and leaves a row in the backup history.

BACKUP DATABASE master TO DISK = N'master_before_move.bak' WITH INIT, CHECKSUM, COPY_ONLY;
RESTORE VERIFYONLY FROM DISK = N'master_before_move.bak' WITH CHECKSUM;

The second statement prints a line that says the backup set on file 1 is valid. Keep the file until the move is done and the instance has run for a day. Delete it yourself afterwards. SQL Server never removes backup files. The backup also leaves a row in the msdb backup history, which you can leave.

Steps to Move the Master Database

1. Open SQL Server Configuration Manager and choose SQL Server Services.
2. Right-click SQL Server (instance name), choose Properties, and open the Startup Parameters tab.
3. Select the -d entry, type the new path of master.mdf in the box above the list, and press Update.
4. Do the same for the -l entry and the new path of mastlog.ldf. Press OK.
5. Stop the SQL Server service. Copy the two files to the new folder, and keep the old files where they are.
6. Grant the service account full control on the new folder (next section).
7. Start the service. Run the first query again and check that both files show the new paths.

Changing the parameters while the service runs is fine. They take effect only at the next start. That is why the order matters: edit, stop, move, start.

Check the Result

After the start, run the first query again. Both rows must show the new folder, and the state must be ONLINE. The SQLArg0 and SQLArg2 rows of the second query must match. Then read the start time in sys.dm_os_sys_info. It must be later than the move.

Give the Service Account Access

The new folder must let the SQL Server service account read and write. A copied file takes the permissions of the new folder. A file moved on the same drive keeps its old ones. A missing permission is the usual reason a move fails with an error such as 17204, FCB::Open failed. Ask SQL Server which account to grant.

SELECT servicename, service_account FROM sys.dm_server_services WHERE servicename LIKE N'SQL Server (%';

Grant that account full control on the folder, for example with icacls "E:\SystemDatabases" /grant "NT Service\MSSQL$SQLDEV:(OI)(CI)F". Run it in an elevated command prompt (cmd.exe). In PowerShell, put the account in single quotes. A copy tool that keeps permissions, such as robocopy with /COPYALL, also works.

If the Instance Won’t Start

The usual cause is a wrong path in a startup parameter or a missing permission. That’s why you kept the old files. Open Configuration Manager again, which works while the service is down, and fix the parameter. Then start the service. The error log and the Windows Application log name the file that failed.

What About the Other System Databases?

Model and msdb move with ALTER DATABASE ... MODIFY FILE, a stop, a file move and a start. Tempdb needs no move, because SQL Server recreates it at every start from the recorded paths. The Resource database is different. On this instance its two files sit in the Binn folder next to the program. They aren’t in the folder that holds master. Leave them there. Microsoft doesn’t support moving the Resource database.

Is It Worth Moving?

You could argue that it is rarely worth the trouble to move the master database. Master is small and stays in memory, so a faster disk should change little for it. The real reasons are a full drive, a company rule to keep system files apart, or a server migration. Don’t expect a speed gain.

Moving master is also not the way to copy a server. A master database taken from another server brings that server’s logins, linked servers, settings and name with it. To copy a server, script the logins, jobs and linked servers, and recreate them on the new one. Moving master doesn’t change where new databases go, either. That default is a separate instance setting, under Database default locations in the server properties.

What to Remember

To move the master database, remember that its location lives in the startup parameters. Edit -d and -l, stop the service, move the files, and start it. Grant the service account access first, and keep the old files until the instance runs. Then check the new paths with sys.master_files.

A system database is not a file you move, it is a path you tell the service about.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.