Here is a recent oops for all to enjoy. My expense.. have fun.
Upgrading a server to Windows 2000 and SQL Server 2000 and additionally expanding the drive sets for the data and log files.
This was an old install prior to my arriving here (actually I am the first DBA on site and this install was done by non-database staff). So, the default databases were all D: and the newer databases were on E: and Logs on F: The only twist is that the TempDB was moved to E: and F: as space was constrained on D: and TempDB was growing. So, now that the stage is set.....
Knowing that I had good backups, both SQL Dump to disk and BackUp Exec of Dumps and DB (hey, you never know), I decided to blow away the arrays once we did the OS upgrade and then upgrade SQL. Sounds good. OS upgrade flew. No problems. Go to start the SQL Server upgrade from 7.0 SP3 to SQL Server 2000 but, the service won't start. Hmmm. Realizing that I had blown away the TempDB, and also knowing that it will recreate itself when starting the service, I immediately suspected this was most likely the problem as all drives with any aspect of SQL Server were still intact. Verified I could write to the expanded and formatted drives E: and F: and all was o.k. Hmmm again. (Read the end to find out why it would not start.)
Here goes the stupid part. After NOT thinking about this long enough, I figured that something MUST have been corrupted with the SQL install and decided that I would uninstall 7.0, install 2000 fresh and restore the 7.0 master and msdb to the newly installed 2000 installation. Much learned this night!!!
After so happily uninstalling 7.0, installing 2000, verifying everything was working well I decided I would restore the 7.0 master database. Here comes the learning. Interesting to note that you CANNOT restore the 7.0 master database to a 2000 installation. No problem, I have a good script for re-creating accounts. I'll just restore the msdb and get my DTS packages and jobs back right?! Notice confidence was waning now. And of course, come to find out you can't restore an msdb 7.0 onto a 2000 installation either. Gee, good thing I script everything and save all my work.
Well, here comes the kicker. The reason this all went wrong, is that I could not get the old 7.0 install to start and I assumed that it was something with the install being corrupted with the OS upgrade when in actuality it was because of the TempDB not getting recreated BECAUSE I NEGLECTED TO CREATE THE PATH (FOLDERS) TO WHERE THE FILES RESIDED. Much work to get everything back but, much learned also.
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot