How to move the system database files in SQL Server

How to move the system database files in SQL Server

In this demo, I’m going to show you how to
move the system databases. In my virtual machine here, all I’ve done so far is install
SQL Server 2008 R2. I should mention: the procedure for SQL 2012
is basically the same and I’ll show you where it’s different later on — there’s only one minor
difference. So as I said, the only thing I’ve done so far is install a
single standalone instance of SQL Server, and it’s running right now. The first step to do the move is to do a little bit of preparation. When I did the install, all my files
landed on the C drive, so what we’re going to do is move them off here to a D drive which I’ve provisioned, and all
I’ve done so far is format it — you can see it’s empty. The first thing we need to do
is make sure that the target location where we’re going to move the files
is accessible by the database engine service account. So the first thing we’re
going to do is set some permissions. By default, Windows like to give Everyone
a little bit of access and we don’t necessarily want that For our SQL data. So what I’m going to do is remove the low-privileged users, and
restrict it to administrators. The next step is to create a folder here and what I’m going to do is I’m going to
pull the instance ID from here and create a new folder here with the name I like to put it in a subfolder because even
though a machine many only have a single instance now it’s usually a bad idea to assume that that’s
always going to be the case. So if you just land the files here, and not
in a subfolder, you can cause a great deal of confusion later on if you decide to put
a second instance and put your data on the same drive.
So I’ve created the data container here, and what we want
to do is make sure that the service account has the correct permissions. So we’ll
go into the Security tab on there and click edit; and we’re going to match it to what
we started with here. So I’m going to go up a level, and show
the security settings for the data folder there and the only difference right now is this local group for the database engine service account,
and it has full control over the files and folders inside that subfolder.
So what we’ll do is we’ll create the permission here and I’m just going to search for it and it’s way down at the bottom it’s SQLServerMSSQLUser and we’ll give it Full Control and we’ll OK through there.
Alright, so now we’ve got our data container ready to go and our database engine should be able
to access it. I’m not going to do it here, but
if you want to test to make sure that it has access, what you can do
is copy a small backup file into here and then run RESTORE … HEADERONLY from Management Studio, just to make sure
that SQL Server can read the file. But I’m not going to do that here. The next step is to open up Management Studio and run a few commands to tell SQL Server
where to find the files after we move them. Because right now we can’t even copy them.
I’ll just show you that. Copy this and I’ll just try to copy it here for fun, and you can see
that the files are actually locked by SQL Server right now, so we can’t even
read them. What we’ll have to do is in advance, tell SQL Server where to find
the files after we move them, shut down the server — sorry — shut down the service, and then move the files, and then
restart the service. So in order to tell SQL Server where the files are moved to, we have to run an ALTER DATABASE
command in order to tell it the physical name — sorry — the logical name
of the file, and then the new physical location of where
we’re going to put it. I should mention now that there’s two different methods we have to use to
move all four databases. master is different than the
other three: if you read my post about what’s in the system databases, you
know that the master database contains the paths of all the other databases
in the instance. Which is why there’s a different method to move master than the other three.
So what I’m going to start by doing is moving model, msdb, and tempdb
first, and I’m going to do two instance restarts. You can do this all in one shot, but just for demonstration,
I’m going to do it in two. So what we’re going to do is we’re going to fill in these values for tempdb. I’m going to go into database properties, and in the Files page, the logical file names here are what we want,
so I’m going to grab these and you can see they’re still on the C drive and I’m not sure if I can copy these,
let’s see if I can Okay, so there are the file names, but we need to specify the new location as a fully-qualified path. I’m
going to grab our new location here and paste that in. Okay, now I’ve already gone ahead and done the same sort of thing for
the other two databases I just need to copy this in here. So now, we still haven’t affected the instance at all yet. As soon as I run this script, the clock
start ticking because if the instance crashes or restarts for any reason
we’ve now told it that it should look for the files in these locations, but we
haven’t been able to move them yet, because they’re still locked. So as soon as we run this — I mean I don’t want you to rush — but just know that once you do run this, you are kind of
vulnerable until the files have been moved. So what I’m going to do is run the script, and you can see in the message here, it says the
file has been modified in the system catalog, and the new path will be used the next time the database is started. Now in this case, since we’re dealing with the system
databases this means on instance restart. So I’ve told SQL Server to look for model, msdb, and tempdb
in our new location. Now what I’m going to do is stop the service and I’m now going to physically
move the files. Actually, I’m going to copy them. And you can see that now that the
service is stopped, we are able to actually copy the files. And now what I’m
going to do is start the instance again. Now, we should be able to get into the system databases — and you can see the
system tables there. And we’ll just go back into the file system and let’s try and copy this one somewhere else. And we can’t. We can see that SQL has
locked the new files, which is good. So what we’ll do is we’ll go in and remove the old files.
You can see that I did not actually move the tempdb files, and this is because
tempdb gets recreated every time the instance restarts. So there’s no real need to copy tempdb because it just gets recreated.
So I can go ahead and remove those as well. So as I told you before that I was going to do this
in two stages, I still can’t copy the master database —
it’s still in its old location. So this is the second procedure, which we’ll
use to move master. The way we do this is completely different. What we have to do
is open up Configuration Manager, go into Properties for the server
we’re interested in, and this is where it differs between 2008 R2 and 2012 — the
interface has changed slightly hear. In 2008 R2, we can go into the
Advanced tab, and then the Startup Parameters setting.
In 2012, there’s actually a tab dedicated for startup parameters. Seeing this demo here, it should be
obvious how to proceed in 2012. So what I’m going to do is I’m going to edit this. From the blog post
I mentioned, when you start SQL Server, you have to tell it
where to find master, and then master contains the paths
of all the other databases. You can see here in the startup parameters the location of the .mdf
and the .ldf files for master. What we’re going to do is specify the new path
to the data file and the log file. When we accept the changes, we don’t have to restart the
instance yet — in other words, it doesn’t take effect immediately, we have to restart the instance for it
to take effect. So I’m going to grab this path here, and I’m going to edit these. Be very careful about there not being a space between here, so this is dash d, and then immediately the path without a space.
So I’ll paste in my path there and then you can see we have the dash e for the error log and then dash l for the master log file. And I’ll paste in the path there. And that’s good. And we’ll say okay. It gives us a warning
that the changes we made won’t take effect until we restart. So now what we’re going to do is
pretty much the same thing as before. We’re going to stop the instance,
we’re going to copy these files, and paste them here. You can see that we did manage to copy
them because they weren’t locked. And we’ll restart the instance. So that started successfully, which is a good sign, and we should be able to get rid of these now. If we go back into Management Studio, we can see Properties
on the master database, and we can see that the page has changed
to the new location. Thanks for watching!

Danny Hutson

14 thoughts on “How to move the system database files in SQL Server

  1. Thank you very much for sharing this video , it is indeed helpful, Great Work, Keep it up.

    Is there any other way to moving or copying file rather than using code, though code is very much easier but, I saw an option as " Copy Database" I want to learn that as well. If you have any related links then please share.

  2. Hi! I will do this procedure tomorrow on a SQL cluster. Would you say that its basically the same procedure or does it differ a lot?

    The only difference i can see is that you have to shut down all SQL server resources in failover cluster(all except cluster IP).

  3. making it easy to copy and paste…

    ALTER DATABASE tempdb MODIFY FILE ( NAME = 'tempdev' , FILENAME = 'new_pathtempdb.mdf' );
    ALTER DATABASE tempdb MODIFY FILE ( NAME = 'templog' , FILENAME = 'new_pathtemplog.ldf' );

    ALTER DATABASE model MODIFY FILE ( NAME = 'modeldev' , FILENAME = 'new_pathmodel.mdf' );
    ALTER DATABASE model MODIFY FILE ( NAME = 'modellog' , FILENAME = 'new_pathmodellog.ldf' );

    ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBData' , FILENAME = 'new_pathMSDBData.mdf' );
    ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBLog' , FILENAME = 'new_pathMSDBLog.ldf' );

  4. Really good video.very very helpful. thank you so much for sharing this. please  share all topics videos if you have then those will be helpful for every dba learners… 🙂

  5. This is a very useful information. I've been struggling to find how to do this online, but there are not too many information out there about this. You have saved my day! thanks mate

Leave a Reply

Your email address will not be published. Required fields are marked *