Changing physical locations of all DBs

I got a request this week from an application engineer. The request was to move ALL physical database files from one physical drive to another physical drive on the same machine.

Hmmmm, I have never moved ALL .mdf files from one place to another at one time. One of my teammates told the application engineer that it may take some time. He want to detach/move/attach all the DBs.

I knew this wouldn’t work because:

1) it would take forever

2) how are you going to detach the master DB?

No Detach for YOU!

So my solution contained 3 parts:

  1. Change the location of all the files inside SQL
  2. shutdown and move the files
  3. Change SQL’s startup parameters for the master database

Easy as π.

I used some good old dynamic SQL to create an ALTER DATABASE script for each db (except the master)

--first, let's just take a look and see where our files are living
SELECT name, physical_name,
CASE
WHEN TYPE = 0 THEN 'DATA'
WHEN TYPE = 1 THEN 'LOG'
END AS FileType
FROM sys.master_Files
 
--Now let's create those ALTER DB statements
SELECT 'Alter DATABASE ' + d.name + ' MODIFY FILE ( NAME = ' + f.name + ', FILENAME = ' +
 
--here is where you want to put where you want to move your files
 
' ''c:\NewFolder\'+
right(physical_name, charindex('\',reverse(physical_name))-1 ) + ''' ) '
FROM sys.databases d
inner join sys.master_files f on d.database_id = f.database_id
where 
 --remove this if you want both DB AND log file
 type = 0 
--and keep this part, dbid 1 is master
 and d.database_id <> 1
 
--Check again to see if the physical name is changed
select name, physical_name,
case
when type = 0 then 'DATA'
when type = 1 then 'LOG'
end as FileType
from sys.master_Files

After you run this, you should see this message a bunch of times:

The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.

Now you can shut down your SQL services and move the physical files to their new location.

Once you have all the files moved over (including the master) you’ll need to change the startup parameters.

To do this:

  1. open up the configuration manager
  2. Right click on your SQL instance –> properties
  3. Click on advanced –> startup parameters
  4. Change the location of the master .mdf
Change the location here
Once you have updated your startup parameters, you should be good to go. I want to stress “should” here because you have to make sure that your service account has access to the new location. If it doesn’t, SQL won’t start.
I tired this a couple times on my test machine and then in the production environment. I was hoping to “wow” the customer with the speed of completing this task, unfortunately their disks were really slow and moving the files took the majority of the time.

What’s your build number?

Without looking, name your most important server. Or if you have a bunch, name the top 2 or 3.

Now tell me what edition you are running. Not too hard. “SQL 2008 R2 Standard” or whatever.

Again, with out looking – what’s the build number? Hmmmm. 10.5…something or other.

Now imagine your server gets hit with a bad case of the Hulk Smash’s and it’s gone. Well good thing you have backups.

Now let’s just take our master database and restore that sucker right there on our backup machine. Uh-oh. What’s that error? 3168!?!?! NOO!!!

whoops...

If you need to restore a master database (and let’s hope you don’t), you better know what build it is before you start down that path. What’s the best way to know all your build numbers for all your servers? Ugh…documentation. I know, I know. We all hate it but in this case it’s going to save you a headache.

Granted, you can get the build number from the error message above but do you really want to spend the time adding a service pack to your backup server before restoring your master DB? I’m sure the big boss man doesn’t want to have extra downtime.

I use a department wiki that has facts about all the production servers. Sometimes it’s a pain to keep updated but it’s necessary.

Do you keep your build numbers documented?