Adventures in moving SQL Server system databases

I recently completed the task of moving the system databases of a Microsoft SQL Server 2008 instance to a different drive letter.  The whole process is described in a brief document on MSDN.  It is literally just a few SQL commands, a few moves of some files, and a restart of the services.  In my case, it all went pretty smoothly until
I made a typo in one of the commands and ended up tracking it down for about an hour!

The reasons for moving a system database to a different drive letter or partition can vary by the setup of the company's physical hardware.  Sometimes there is a performance benefit if the other drive letter points to a faster physical drive.  Sometimes the second drive letter doesn't make one bit of difference in terms of performance because of virtualization or clustering.  In our case, the reason for the move was because the server had been configured with only one partition to start with and later on it was given another larger partition for the data to sit on.

Our experience in the past was that some databases occasionally went overboard in their data collection and ate up all the available space.  In the previous experience, the database files were stored on a non-system partition, so if all the disk space was used it would halt the SQL Server service, but the operating system could still be accessed.  We wanted to make sure that the new server was configured the same way. (Yes, I know we could have set maximum sizes on the databases to achieve even better stability but that isn't something that we'd done before.  So, as part of a phased approach to stability we decided to protect the operating system first, then worry about the SQL Server service as part of another task at a later time.)

Earlier in the week, I'd moved the non-system databases (aka, user databases) to the new drive (D:) by detatching them with SQL Server Management Studio (SSMS), moving the files, and then reattaching the databases.  I'd also changed the default location where new databases would be created by right-clicking the server name in SSMS, going to Properties, selecting the Database Settings page, and changing the Database Default Locations as described in the MSDN article "How to: View or Change the Default Locations for Data and Log Files (SQL Server Management Studio)".  Now I just needed to move the master, model, msdb, and tempdb system databases.

I started with the MSDB database, taking careful note of what I did and making sure that everything went as it should.  I excuted the following command:

ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\Databases\MSDBData.mdb')

Some particularly astute IT Gurus will already have noticed my typo, but I didn't at this point. I moved the file to the new location, restarted the SQL service, started SSMS and clicked on the "New Query", then issued the following query:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files;

The query executed as I expected, showing that the MSDB database was in the new location and was online.  I moved the Log file (.LDF) in a similar manner, checking again that it completed successfully.  At this point I was feeling comfortable with the process so I moved the other databases (Note: The master database has a different procedure to move it.  Read the MSDN article and understand it fully before performing your own move operations.)

I then tried to follow the steps to change where the SQL Agent log files were stored, but was unable to get the SQL Agent to start.  That was a bit troubling, but not a huge problem since I knew that we didn't have any business critical SQL Agent jobs that needed to run.  I also knew that I had timed this maintenance window immediately after the nightly backups had finished and while no one was using the system, so if I had to restore the system I wouldn't lose any data.  I then tried (for the first time since starting to move the databases) to expand the Databases node in SSMS and it said that it couldn't read from the msdb database!

I looked all over for reasons why it couldn't be accessed.  My search took me to some excellent sites, including SQL Authority by Dave Pinal and his article "FIX : Error 945 Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details".  I clearly had enough disk space.  I had enough memory free, but I restarted the server just to make sure.  It didn't help.  I tried checking permissions on the old and new file structure to make sure they matched.  It didn't help. 

Eventually the answer came in an email from an application that monitors the Windows Event Logs on the server: "FCB::Open failed: Could not open file D:\Databases\MSDBData.mdb for file number 1. OS error: 2(The system cannot find the file specified.)."  It didn't hit me right away, but eventually the light bulb came on and I realized that I'd mistyped one single character when specifying the new location for the database.  Microsoft Access 2003 database files have the extension ".MDB".  Microsoft SQL Server data files, however have the extension ".MDF".

I issued the correct ALTER DATABASE command, restarted the SQL Server, and everything was back on track and following along with the instructions from the MSDN article!

My takeaway: Always have good backups.  Check.  Always have a rollback strategy if things go wrong.  Check.  Always double-check and syntax check your commands before issuing them. Check.  Then, remember that SQL Server data files end in .MDF instead of .MDB and everything will be fine!