Today I’ve been copying a database from one SQL Server machine to another: specifically from SQL 2000 to SQL 2005, for testing purposes. Unfortunately this didn’t go as smoothly as I would have liked, so here are a few notes for anyone else who does this (e.g. my future self).
One aspect of SQL 2005 is that it comes in two versions: 32-bit and 64-bit (x64). You need to match the SQL version to the OS version (i.e. you need 64-bit Windows 2003 to run 64-bit SQL 2005), but the advantage is that you can then use more than 4 GB of RAM, which is useful for big databases (e.g. a year’s worth of point of sales data for a supermarket chain). This isn’t the only reason to upgrade, but it may be quite a compelling one, depending on your circumstances.
Copying tables across goes pretty well; the only quirk is that any timestamp fields in the source database which had the “allow Nulls?” attribute checked lose that attribute in the destination database (i.e. you can no longer enter Null values into those fields). I was able to manually change this afterwards, and the attribute seems to be preserved for all other datatypes, so this is a bit odd.
The import process will let you include views, but they then get converted to tables in the destination database, i.e. they become a static copy of the data rather than a dynamic query that links to the latest information. It’s not too hard to recreate them, although you can’t just copy the SQL query straight across – you need to use the “SELECT” portion of the query in the “create view” window, then specify the name for the view when you save it. In other words, you have to ditch the “CREATE VIEW” clause from the start.
Stored procedures and user defined functions are completely ignored, so you need to re-create them individually; allow time for this! (It took me a couple of hours.) In most cases, you can just copy/paste, but SQL 2005 is a bit stricter about what it will accept. In particular, you now have to use ‘ as a string delimiter rather than “. It also objected to an INSERT INTO query that didn’t specify a destination field list and had the wrong number of fields in the source list. (You can use a subset of fields if you specify them individually, and you can copy all the fields across without listing them, but you can’t do both at once!) Personally, I always prefer to list the fields individually anyway, to avoid this type of problem.
Once the database has been prepared, you then need to actually talk to it, and this is where backwards compatibility can be a bit tricky. There is a “compatibility level” property of each SQL Server database, and generally the most recent three versions are supported. For instance, SQL 2000 gives you the option of level 65 (SQL 6.5), level 70 (SQL 7.0), or level 80 (SQL 2000). In turn, SQL 2005 gives you the option of level 70, level 80, or level 90 (SQL 2005). I’d normally recommend using the “native” level, but you may find that there are some differences in query processing; for instance, the *= and =* operators for outer join are no longer supported at level 90. In this case, I set the compatibility level to 80, to match where it came from. (In actual fact, I set this level when I created the database, before I imported any data or created new SPs.)
That’s all well and good, but you need to be able to connect to the server before you can connect to a database on that server. As far as the management tools go, you can use the SQL 2000 tools to talk to SQL 7 and SQL 2000 servers, and you can use the SQL 2005 tools to talk to SQL 2000 and SQL 2005. You can’t use the SQL 2005 tools to talk to a SQL 7 server, and you can’t use the SQL 2000 tools to talk to a SQL 2005 server. I have both sets of tools here, so that’s not a problem for me. However, if you have a client application that uses the same libraries then it will have the same limitations. In this case, when I run the (3rd party) front-end application, it comes up with an error message:
“Error Number:-2147165949
[Microsoft][ODBC SQL Server Driver][SQL Server]To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO).”
So, I think I’ll have to keep that database on SQL 2000 for the time being. Hopefully the company concerned will add support for SQL 2005 in due course.