Potential Approach to Moving Small SQL Server Databases to Lower Versions

Recently a team-mate of mine needed to move a SQL Server database off of an Express Edition version to a Standard Edition so that it could better scale. Unfortunately, the database was currently on SQL Server 2008 R2 Express while the targeted server for transition was SQL Server 2008 Standard (i.e., not R2). This raised the question, is it possible to downgrade a SQL Server database? Most of the answers that we saw indicated that this isn't "officially" supported by Microsoft. Through some searching and trial and error, we did come up with a process that worked in our tests. I'm far from saying that it will work in all, or even most cases, however, I wanted to share the process we came up with in the hope that it may help someone else in a similar situation.

This process is draws upon a couple posts in different user forums who's links are provided at the end of this post. This process will work better on smaller databases.

The high-level approach uses SQL Server Management Studio's Generate Scripts task to create separate schema and data scripts, then creates required log-ins in the target database, runs the schema script to create the users\schema, temporarily disables all constraints, executes the data script, and finally enables the constraints.

The steps below walk through the process:

On the machine running source database (in our case SQL Server 2008 R2 Express)

1. Open SQL Server Management Studio
2. Select your source Database
3. Select Tasks->Generate Scripts.
4. Select 'Script entire database and all database objects', press 'Next'
5. Select 'Save to File' and enter a file name like “Objects_Schema.sql”
6. Click on the 'Advanced' button
7. Select 'Script for Server Version' and select the version you want (in our case SQL Server 2008)
8. Select 'Type of data to Script' and select Schema Only
9. Click 'OK', then Next and create the script
10. Run the wizard again to export only the data
11. Select your source Database
12. Select Task->Generate Scripts.
13. Select 'Script entire database and all database objects', press 'Next'
14. Select 'Save to File' and enter a file name like “Data.sql”
15. Click on the 'Advanced' button
16. Select 'Script for Server Version' and select the version you want (same version as above)
17. Select 'Type of data to Script' and select Data Only
18. Click 'OK', then Next and create the script
19. Copy the resulting files to the target machine

On the target machine (in our case SQL Server 2008)

1. Edit the Schema Only script to set the location of the data files to be created.
2. Create any log-ins referenced in the source database
3. Run the Schema Only Script
4. The data script does not follow referential ordering when inserting data. Therefore we have a choice to edit the data script to put the inserts in referential order, or to do the DBA cringe-worthy task of disabling all constraints. If you choose the quick path, execute the following T-SQL command as the object owner to disable all of the constraints. Be careful you know what you're doing here!
5. exec sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
6. Run the Data Script using SQLCMD as they can be really large and not load in SQL Server Management Studio.Log the output to a file.
7. Resolve any errors if they are reported
8. If you previously disabled all the constraints, Run the following T-SQL command to enable them
9. exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
10. Perform typical; smoke testing of your database\application to ensure everything transferred OK

 That's it. I'd appreciate any feedback or improvements on this process and hope it helps someone.

Source References:

• http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/4daae3e5-15ab-4929-a122-e1bab420310e
• http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql