Migrating from SQL Server 2000 to 2005?

The current advice (which will leave you with a broken database)

  • Install SQL Server 2005 directly over the SQL 2000 instance
  • Restore a SQL Server 2000 backup of your database on to a new 2005 server
  • Detach the underlying database files from 2000 and re-attach them to 2005
  • Script out all the objects and data in the database and rebuild everything on SQL 2005

The first three methods suffer from this drawback - objects may be broken by the move to SQL 2005 and you will not be notified. The last method has some merit but is virtually impossible to do without the ability to order all the scripts in the right dependency order for a new build.

The DB Ghost Scripter and Builder tools are the solution

This is an explanation of the problem, and it's solution, from one of our largest customers - the Australian Government:

Unfortunately, the SQL Server 2005 Upgrade Advisor is limited

In theory, the SQL Server 2005 Upgrade Advisor should tell you about all the issues with your current database. In practice, it finds very little and tells you to check a whole heap of specific changes between SQL Server 2000 and 2005. Basically, SQL Server 2005 is a lot stricter than 2000 and I would be surprised if you did not have at least one SQL Server 2000 object that fails to compile in 2005. Checking manually for all possible issues would be time consuming and error prone, but there is a better way - use the DB Ghost Builder.

I recommend that you do the following:

  • Use the DB Ghost Data and Schema Scripter to script out your current SQL 2000 database schema (not data) into DROP/CREATE scripts.
  • Rebuild the schema of your database (using a different name) on the same SQL 2000 instance using the DB Ghost Change Manager - this will tell you if your database is simply broken under 2000 (most people discover there are syntax and dependency issues that they were previously unaware of - the Change Manager merely highlights the problems and is not the cause of them)
  • Fix any build issues discovered, by modifying the scripts, and rebuild. Keep doing this until you get a clean build and keep a note of the scripts that were modified.
  • Now build the schema, using the same set of scripts, against a native 2005 instance which has a compatibility level of 90 (more about that later). DB Ghost will list all the objects that failed to compile and these are the ones that are broken by the move to SQL 2005.
  • Fix the problems in the scripts, keeping a note of which ones you changed and keep rebuilding until you have found all the problems.
  • Backup your 2000 database, restore it onto SQL 2005 (or use detach/attach) and set the compatibility level to 90.
  • Run all the fixed-up scripts against the SQL 2005 database to correct all the SQL 2005 problems.
  • You now have a solid SQL 2005 database.

Background - The SQL Server 2005 compatibility level trap

Like previous upgrades to SQL Server, the latest version can support previous versions. This is done by setting a compatibility level for the database. SQL Server 2000 is compatibility level 80 and SQL Server 2005 is compatibility level 90.

If you restore a SQL Server 2000 backup to create a SQL Server 2005 database (or use detach/attach or upgrade the database engine to 2005), the database will have a compatibility level of 80. You can then go to the database properties and change the level to 90.

But here is the trap.

When you upgrade the compatibility level SQL Server WILL NOT INFORM YOU ABOUT OBJECTS WHICH NO LONGER WORK UNDER SQL SERVER 2005.

For example, we had a stored procedure that compiled ok in SQL Server 2000 but failed in 2005 because we had this line.

CASE WHEN pir.PerformanceIndicatorResultStatusCd = 'E' OR.pir.PerformanceIndicatorResultStatusCd = 'A' THEN 1

Msg 4104, Level 16, State 1, Procedure prcPerformanceReportNavigator, Line 145
The multi-part identifier ".pir.PerformanceIndicatorResultStatusCd" could not be bound.

I created a SQL Server 2005 database using a backup which contained this version of the stored procedure. The database restored with compatibility level 80. I changed it to compatibility level 90. I then tried running the stored procedure and it failed.

EXEC [dbo].[prcPerformanceReportNavigator]
@MilestoneId = 118,
@SectorCd = 'p',
@OutcomeAreaCd = 'PL'


Msg 4104, Level 16, State 1, Procedure prcPerformanceReportNavigator, Line 144
The multi-part identifier ".pir.PerformanceIndicatorResultStatusCd" could not be bound.

Martin Lange, Technical Team Leader (www.dest.gov.au)

This is just one of the many ways in which the DB Ghost tools can help you

Take a look at the rest of the site to get more information on the full product range and some more details on what we call the "DB Ghost Process"