If you have landed on this blog post, I can only assume you have run into a few issues getting a database that is has been built in an newer version of MS SQL Server to restore onto an older version of MS SQL Server.
Unfortunately you can’t simply just take a backup and restore it.. as this simply doesn’t work and will throw and error… but there is a way!
First you need to generate a script for the database schema, run that first to get the table data.
Do this by..
- Opening up SQL Server Management Studio
- Right click on the database you want to transfer
- Tasks -> Generare scripts
- In the set scripting options section click on Advanced
- Then change the “Script for SQL Server version” to be the version of the database you and transferring it to e.g. 2008 R2
- Generate that script and it will generate a large .sql file with the entire database
Then run this script (check the database name is correct in the top of the file etc) on the target database to generate the tables etc. They will be blank, next you need to transfer the data over.
The next issue I ran into here was that foreign key constraints meant that some rows couldn’t simply be inserted, for example if you are trying to insert a row into Products that is linked to the Categories table.. the Product may get inserted before the category exists and will cause an error. You basically need to disable all table constraints to enable all data to get into the database.
Run this query to disable constraints across all tables so that all data would get inserted regardless of FK’s etc:
EXEC sp_MSforeachtable @command1=”ALTER TABLE ? NOCHECK CONSTRAINT ALL”
Then go back to SQL Management Studio on the original newer database and export the data by:
- Right click the database
- Tasks -> Export Data
- Follow the wizard through choosing the original database and also the database you wish to transfer the data to (the only we created with blank tables above)
- Run the wizard and with any luck all the data should be transferred
Then enable the constraints again via this query:
EXEC sp_MSforeachtable @command1=”ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL”