Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Sandwiches

Sqlsandwiches is a tool for me to communicate what I have been learning to the SQL community.

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 p.

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 type = 0 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.

Comments

Posted by john barnett on 8 July 2012

Had to do something similar, to move data to a new SAN drive. What I did was:

1. take full backup of server, including system state data and application/database files and do a test restore

2. stop SQL and application services, set them to manual startup

3. Use robocopy to copy files across preserving timestamps, ownership, attributes and ACL's from old to new SAN drive

4. Use Disk manager to rename original volume letter to something free, then reboot

5. rename new volume letter back to original volume letter, reboot again

6. restart SQL services and test.

hey presto, took about half a day.

Posted by David George(tm) on 9 July 2012

I've done this too -- roughly 200 databases per server instance and I moved 4 instances. Scripted out the filename alter's and the detaches, then robocopied the files to the proper place, and another script to attach.I had a 3 hour window and we finished just inside it.

Posted by F. Dwarf on 9 July 2012

Nice strategy, it never crossed my mind to perform such task this way.

One question, though: what exactly are the differences between this approach and detaching/attaching the databases? I mean, for the user databases -it's clear (or I think it is) that the procedure to move the system databases would have been the same.

Cheers!

Posted by Tony Palmeri on 9 July 2012

.

Posted by Tony Palmeri on 9 July 2012

You can minimize downtime by doing a full backup first, and stay online while you physically move the full-backup file.  Once that long-drawn-out process is done, you can do a quick log backup, go offline, detach, restore from the new location and go quickly back online.  I'm sorry I don't have all the lengthy details, but I just did a process like that recently were I had to physically relocate the database to a whole different server on a different subnet in a different building.  The large file-copy took nearly an hour to complete, but the total time "offline" was only a few minutes.  Of course, I had a lot of other issues to attend to, because the database was on a new server with a new server-name at a different IP address. But I imagine this same technique would minimize downtime in your situation for the same reason - - the long time it takes to physically copy large files, either between drives, or across the network.

Leave a Comment

Please register or log in to leave a comment.