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.
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.
- Open SQL Server Configuration Manager and choose SQL Server Services.
- On right hand pane, right click on “SQL Server (<Instance Name>)” and choose properties.
- Go to “Startup Parameters” tab.
- We need to update two parameters:
- -d<new path>\master.mdf
- -l<new path>\mastlog.ldf
- After modifying click OK.
- Stop SQL Service.
- Move the physical files of the master database to a new location.
- Start SQL Service.
- 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)
Is there a performance benefit to moving (other than backups) the master db from C: to san?
Haven’t got a chance to test this. Will the SQL Server let you to change the startup parameters when the service is running?
Yes but they don’t take effect until you stop and restart the SQL Server service
@Jack – Thanks for helping @Biju
You are correct. Restart is needed.
can we able to move log file path also from one path to another path through SQL server configuration manager.
Yes you can do that. Restart is needed.
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.
What about the resource database, haven’t they moved that too
I can perform these steps to #8, the database won’t restart after changing the startup location and moving the files. Thoughts?
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.
To ensure permissions etc. are transferred I use ROBOCOPY /COPYALL when moving the files.
If I will move master db and other bases so when I will want co create new database will be used new location?
Yep! I did just that