SQL 2000 Migration

There are many instructional posts and blogs on migrating sql 2k to newer versions. My little experience migrating a 40 GB legacy db to SQL 2014:

The client didn't have a dba and the system admin just couldn't get it going. He was stuck on error and warning messages that he didn't understand and apparently, his google-foo wasn't good either. So they asked me to help and here's what I did.

First off, as a database developer, my db admin skills weren't exactly back-of-the-hand knowledge. I had to spend a few hours doing research and testing. The Microsoft database migration tool came up as helpful, if not necessary. Except, it wasn't. It just added confusion and fear to a relatively straight forward process. It gave warnings about slow restoration, about Full Text search not being supported... can't remember the last one, but basically, I ended up ignoring the tool.

The client environment was:
The production 2k server A development 2008 R2 server A new production 2014 server

All servers are vm's running appropriate versions of Win Server.

Working with the client's sys admin, I first had to have admin permissions on both the OS and the database levels with my vendor domain account.

The machines had to have access to shared network resources.

SQL server, for whatever reason, does not utilize networked drives.

So, the 2k backs up the database to a local drive. The back up file is copied to a network share via batch script. Old back ups are maintained via t-sql written into SQL Agent Jobs.

I first created the new database on the 2008 server with the same name as the original database. I set the compatibility level to sql 2000. I used the restore wizard to restore a copy of a full backup to the 08 server, setting the option of "With Replace". The target file system was the same so I didn't have to change anything and drive space was adequate. It took maybe 15 minutes to restore the 2k db to the 08 db without a hitch.

Once restore is done, set the compatibility mode back to 2008, then create a full backup of the new database. Copy that file onto the share, then down to the 2014 server.

Create the new database on the 2014 server with the same name. Set compatibility mode to 2008. Using the restore wizard in '14, select the source file, set the target file paths if necessary, set the overwrite option and uncheck the tail-log backup. This last one is because being a new database, there is no tail-log. Click OK to restore, wait 15 minutes to completion, reset the db compatibility mode to 2014 then validate the data.

A useful sql statement in this process was:
ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE

A prior attempt at restoring the database onto the 14 server created a partial restore that I couldn't rename, take offline, detach, whatever. Someone or something was locking it and since it was a bad project, I was ok with forcing the disconnect. I think it may be useful in the near future when we go ahead and redo these steps on the production 2k server at final cut over.

So far so good, the application vendor should be done validating the data and application functionality soon. All that's left is planning the final cut over and redoing these steps using the latest dataset. Fingers crossed.