SQL Server backup & restore across versions

This may be a short post in stating the [insert your favourite expletive here] obvious, but you can restore a SQL Server 2000 backup on a SQL Server 2008 database and that’s a good way to do a version migration.  However that doesn’t mean your functions and stored procedures are going to work, so before you do this you should definitely definitely run the SQL server 2008 upgrade advisor. This checks for all the known problems in the database and gives you a report of the issues you need to address. 

That’s a good place to start, but you also need to remember to check all the code in the applications that are accessing this database.  Assuming for a moment this database is something you have written in house, you can check that by turning on profiler and tracing all the SQL hitting the database, and then test the output using the same upgrade advisor.  This exercise needs to a good as coverage of you application as possible, perhaps including any setup scripts.

If you are migrating a third party application then check with the vendor that it’s OK to run on SQL Server 2008 and refer them to me if it isn’t yet so I can get them the assistance they need.

Making the further assumption that you have done all of this and have now restored your legacy database onto SQL Server 2008, and that you are moving from then there is still some more work you should do:

1.  If possible change the compatibility level of the database to SQL Server 2008 so it runs fast and you can use all the new stuff.

2. If you are migrating form SQL Server 2000, change the torn page error detection method to checksum in database properties –> options, as it is a is a much better test. 

BTW bear in mind although this option is set immediately it will only work when all of the pages have been read in the new version of SQL Server (which will create the checksums themselves for each page). DBSCC checkdb is one way to achieve that, and you might want to do that anyway to ensure all is well after the migration.

3. Rebuild all of the indexes, if you are migrating form SQL Server 2000.

There’s a ton of other stuff you might  want to do as well, but this is a pretty good start.

Technorati Tags: SQL Server 2000,SQL Server 2008,backup,restore,migration,upgrade,checksum,compatibility mode