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.

SQL SERVER - How to Migrate Master Database to New Location? masterdb

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.

Solarwinds
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.

SQL SERVER - How to Migrate Master Database to New Location? mig-master-01

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

SQL SERVER - How to Migrate Master Database to New Location? mig-master-02

  1. After modifying click OK.
  2. Stop SQL Service.
  3. Move the physical files of the master database to a new location.
  4. Start SQL Service.
  5. 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)

Solarwinds
, ,
Previous Post
SQL SERVER – Learning DATEDIFF_BIG Function in SQL Server 2016
Next Post
SQL SERVER – How to Install SQL Server Management Studio (SSMS) From Command Line?

Related Posts

10 Comments. Leave new

  • Is there a performance benefit to moving (other than backups) the master db from C: to san?

    Reply
  • Hi,
    Haven’t got a chance to test this. Will the SQL Server let you to change the startup parameters when the service is running?

    Reply
  • can we able to move log file path also from one path to another path through SQL server configuration manager.

    Reply
  • Will this work to move the system databases to a new server with the same name. For example MySQLServer running SQL 2014 on Windows 2008 and move all databases including system to a new server named MySQLServer running Windows 2012.

    Seems like it should work if the only change is the OS version.

    Michael

    Reply
  • Perry Whittle
    July 27, 2019 11:02 am

    What about the resource database, haven’t they moved that too

    Reply
  • I can perform these steps to #8, the database won’t restart after changing the startup location and moving the files. Thoughts?

    Reply
  • After moving the master log file I got an error “event id 17204: FCB::Open Failed”. I resolved this via PowerShell, using Get-ACL to fetch the permissions of another SQL owned log file and Set-ACL to put these same permissions on the moved log file.

    Reply

Leave a Reply

Menu