I have a SQL2008 database that needs to be restored to a SQL2005 instance.
I have gone through the "Generate scripts..." wizard, set it for SQL2005 compatibility, and generated a 62MB SQL script.
When I run it on the SQL2005 instance, it throws all kinds of errors, and some of them are really strange in that they describe an invalid database.
- FK constraints are wrong. It's trying to create FKs on columns that don't exist.
- It's trying insert records with duplicate key errors.
- It's trying to create the same objects twice.
Any idea how this could happen? This SQL script was generated by SQL Server Management Studio just minutes before I tried to restore it, and was not modified.
Why would this generate an invalid SQL file? Doesn't it just describe the SQL2008 database, which is presumably valid since we're using it?
In particular, the duplicate key insertion errors mystify me. If there's a key constraint in the SQL script, then there must be the same thing in the SQL2008 table. So how could we get rows in there that violate that key constraint?
It's something to do with the step-down to SQL2005. I ran the script to be SQL2008 compatible, then turned around and ran the resulting script on the same SQL instance it just came from, and it ran without error. So the problem comes when I try to make it SQL2005 compatible.
I re-genned the script to be SQL2005 compatible, then turned around and ran it on the SQL2008 instance. It worked fine. So...
- SQL2008 Script --> SQL2008 Instance WORKS
- SQL2005 Script --> SQL2008 Instance WORKS
- SQL2005 Script --> SQL2005 Instance DOES NOT WORK
You can't create a script for SQL2005 and run it on Express. I switched to Standard, and it worked fine.