Skip to main content

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:


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!


Popular posts from this blog

Change a User's Password in ASP.NET Membership when you don't know the original password

I ran into a situation where I needed to regain access to a site using ASP.NET membership security, but I'd lost the passwords to the user accounts, the security questions were not implemented, and the password reset email did not work either. This article from StackOverflow helped regain access to the site.

How to Debug Local VBScript

For a long time, I didn't know any better way to "debug" a VBScript than to find where it blew up from the error message and put MsgBox() statements right before it. Eventually I needed a better way, and I found this quick and easy tip. Open command window <cscript|wscript> <file.vbs> //X Now when the script throws an error, you have the opportunity to run the script debugger such as Visual Studio 2010. In my case, it I use Visual Studio Professional, but I think it will work with the Express Editions as well.