I have a SQL Server 2000 I need to upgrade to SQL Server 2005. In considering different ways of doing this (side-by-size, overlay, move to a new machine entirely) I set up a SQL 2000 instance on a DEV server and started playing around with the master database. Here are a couple of things I encountered that are worth sharing.
Error Starting SQL Server 2000 in Single-User Mode
I forgot I was using a named instance of SQL Server 2000 on my DEV box. To restore master, SQL needs to be in single-user mode. To do this, you shut SQL down and open up a command prompt. Then you type:
C:\Program Files\Microsoft SQL Server\MSSQL$SQL2000\Binn>sqlservr -c –m
You might realize I’m missing something. I didn’t realize it at the time though. I ran the above command and saw an error message:
Your SQL Server installation is either corrupt or has been tampered with (Error: Instance name exceeds maximum length.). Please uninstall the re-run setup to correct this problem.
Ok. Really don’t like the look of that. What happened?
I forgot to add the instance name: SQL2000 in my case.
The correct command was:
C:\Program Files\Microsoft SQL Server\MSSQL$SQL2000\Binn>sqlservr -c -m -s SQL2000
After Rebuilding Master
After you rebuild master, the password for sa is blank. So be sure you set the password or change to Windows Auth right away.
Restoring Master
Never having tested this out, I copied the backup of master from one machine to another and tried to restore it. The restore went ok, but when I tried to start SQL I got the following error:
17204 : FCB::Open failed: Could not open device D:\MSSQL\data\RobDb.mdf for virtual device number (VDN) 1.
I did a little digging in BOL and other places and it appears that if you are restoring master to a different machine, the disk layout must be the same. If it’s not, you can restore master but you cannot get SQL to start. When I say disk layout must be the same, I mean you must have the same drive letters, same paths, and data/log files should be in the places master expects them to be. If they are not, SQL will not start.
In retrospect I suppose this makes perfect sense. I’ve looked around quite a bit and haven’t found a way around it.