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!