Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP

Hemantgiri S. Goswami is working as a Consultant at Surat, INDIA. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com. He is an Author of the book SQL Server 2008 High Availability.

Moving MASTER Database

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%'

Screen001

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

Screen002

Step 3: Stop SQL Server Service and move database files to new location

Step 4: Restart SQL Server Service, surprised ?

Screen003 

Step 5: This was expected, let’s see what errorlog has to say about this!

Screen004

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

Screen005

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

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...