SQL SERVER – How to Migrate Master Database to New Location?

One of my clients contacted me for On-Demand support with an interesting situation. Their goal was to move ALL databases from C drive to the E drive. They told me that they have done ALTER for all databases (user and system) but it is not working in the master database.

I asked them the steps they did to move the database. They informed me that they have executed the ALTER command for ALL the databases. Here is the command for master database.

ALTER DATABASE master 
MODIFY FILE (NAME = master, FILENAME = 'E:\SystemDatabases\master.mdf');
GO

ALTER DATABASE master 
MODIFY FILE (NAME = mastlog, FILENAME = 'E:\SystemDatabases\mastlog.ldf');
GO

After this they restart the SQL Service and check DMV again.

SELECT name, physical_name AS CurrentLocation, state_desc  
FROM sys.master_files  
WHERE database_id = DB_ID('master');  
go  

The value reverts to old value.

SOLUTION/WORKAROUND

The method to move master database is different with other databases. It looks like the location of master database files has been picked from registry (SQL Startup Parameters). So, we should change mater database file location by modifying the value in registry through the SQL Server configuration manager.

Here are the correct steps.

1. Open SQL Server Configuration Manager and choose SQL Server Services.
2. On right hand pane, right click on “SQL Server (<Instance Name>)” and choose properties.

3. Go to “Startup Parameters” tab.
4. We need to update two parameters:
   • -d<new path>\master.mdf
   • -l<new path>\mastlog.ldf

5. After modifying click OK.
6. Stop SQL Service.
7. Move the physical files of the master database to a new location.
8. Start SQL Service.
9. Verify the catalog view to see if the new location is reflected in the master database.

So, as we can see there is no need to modify the master database location as its automatically picked from the registry.

Have you ever come across such requirement and made this mistake?

Reference: Pinal Dave (https://blog.sqlauthority.com)