SQL Server Data Migration Backward Compatibility

There may come a situation where by upgrading a SQL Server Database, you would need to roll back and restore that Database to an Instance that is of a lower/previous version.

Unfortunately this cannot be done using the traditional backup and restore methods.

Even if there was an attempt to change the compatibility level of the Database to the level you want to rollback to for the target Instance. This will also fail, as the compatibility level of the Database will not change the build number of the SQL Server Instance.

This is because unfortunatley you can only restore a Database from an Instance that has an Instance build number lower than the target Instance. This is also the case for migrating a SQL Server 2008 R2 Database into a SQL Server 2008 Instance.

However there are a number of migration tools on the market and workarounds, to migrate a Database from a higher Instance version to a target lower version. There are a few important things to remember when using one of these alternative techniques.

- There are some data types that are not compatible with this type of migration. You need to be ready to map these data types with a workaround.

- Another issue would be looking at the foreign key constraints; this can cause issues if the tables are not migrated in a particular order. The alternative would be to script out the constraints and script them back in after the migration is complete.

For more information on SQL Server Consultancy please do not hesitate to contact dsp