It may seem unusual migrating from SQL Server 2008 back to SQL Server 2005, but believe me it is useful to know a few gotchas if you do find yourself in this position.
I tried many different ways to migrate back 2008 to 2005. The following steps are what has worked for me quite reliably, but I am sure there are other ways to go about this.
Before outling the steps I should make it clear that the 2008 database was in a shared hosting environment and so there was no database backup facility available.
- Script Objects – script any tables, views or stored procedures from the source database and execute on the destination database.
- Copy Data – create an Integration Services Package to copy data from the source database to the destination database.
Script Objects
Rather than copying the database objects directly with Integration Services, I first scripted the tables, stored procedures, views and user-defined functions with the Generate Scripts option that is available through Management Studio. When creating the scripts there is a settings screen that allows you to target the scripts to 2005.
The scripts created can then be executed on the 2005 database with the objects recreated.
Copy Data
Next we have to copy the data and this is achieved using an Integration Services Package. It was not possible to create the package and get it working properly using Visual Studio 2005, instead VS 2008 should be used.
It is easiest to create the package using the Create New Package Wizard. The wizard guides you through the source and destination database configuration and allows you to select which tables to copy data for.
With the tables selected you should highlight all the tables and then select Edit Table Mappings. This allows the table mapping settings to be applied to all the selected tables (if you do not have a full version of VS 2008 then you may have to do this manually for each table). With the mapping screen I have found it is best to select the following:
- Do not drop tables
- Delete data (rather than append)
- Allow identity inserts
When trying to use drop table and allow identity inserts I have found the identity inserts do not work correctly.
With the package created all you need to do is save and execute it. The data should then copy nicely to the 2005 database!