In my previous post we see how to move MSDB database, today we will see how to move or relocate MASTER database. While moving MASTER database we’ll have to consider few other things like changing start-up parameter for SQL Server Service. I will also mention those stops here for better understanding. Let’s do it step-by-step.
Step 1: Query sys view and note down the existing location for MASTER database
USE MASTER GO SELECT NAME, PHYSICAL_NAME AS 'PhysicalFilePath', STATE_DESC AS 'DB Status' FROM SYS.MASTER_FILES WHERE NAME LIKE 'Mast%'
Step 2: Run alter database command and change the location for database files
ALTER DATABASE MASTER MODIFY FILE ( NAME = MASTER, FILENAME= 'C:\SQLDB\Demo\Master.mdf' ) GO ALTER DATABASE MASTER MODIFY FILE ( NAME = MastLog, FILENAME= 'C:\SQLDB\Demo\MastLog.mdf' ) GO
Step 3: Stop SQL Server Service and move database files to new location
Step 4: Restart SQL Server Service, surprised ?
Step 5: This was expected, let’s see what errorlog has to say about this!
Refer the highlighted section, SQL Server service could not find the files. This is because we have moved that files to new location.
Step 6: Okay, so let’s go and change the start-up parameter. We can do this using Configuration manager.
Step 7: Right click on SQL Server service –> Properties –> Start-up Parameter
Step 8: Make correction in path for Master.mdf and Master.ldf
Step 9: Start SQL Server service, this time it will start.
You are done!!
Note: This is to be done when we have to do relocate databases to new drive, or file organization, or some error which force us to do this.
-- Hemantgiri S. Goswami