How to Move the Master Database in SQL Server 2008 R2
Published: Mar 10, 2014
It's not a frequent occurence but times have arisen in which it's necessary to relocate the master database within your SQL Server. With SQL Server 2008 this is an incredibly simple operation and shouldn't cause you any problems.

This is surprisingly easy, in fact it’s a little too easy really.

Let’s presume our master database is in completely the wrong location:

In fact you can see… it’s actually in the tempDB folder with my tempDB files. Clearly we don’t want it there, so let’s move it.

Note: You can also see the location of your master database files by looking in the GUI or using T-SQL. This technique is documented as part of this article: How to move tempDB in SQL Server 2008 R2.

Go to your SQL Server and Stop the Service:

Once stopped, locate your files and move them (cut and paste works just fine) from your old location to your new location:

Next, open SQL Server Configuration Manager and click on SQL Server Services on the left hand side:

In here, locate the main SQL Server Service, right click, and select Properties:

Click on Advanced:

And you should be able to see “Startup Parameters”:

Clicking the drop down arrow will let you see a nice text box which will tell you exactly where SQL is looking for your master database:

In this case you can see that the drives are:
-dD:\Microsoft SQL Server\tempDB\master.mdf
-lD:\Microsoft SQL Server\tempDB\mastlog.ldf

You simply want to change these to the new location. In my case these are:
-dD:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
-lD:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf


That’s it.

Click “Apply” and you’ll get a little notification box:

Click OK and then simply start the SQL Service:

Your files are now exactly where you wanted them:

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron