Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.