0

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?

Update:

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.

Another Update:

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

Solution:

You can't create a script for SQL2005 and run it on Express. I switched to Standard, and it worked fine.

Deane
  • 247
  • 3
  • 9
  • Can you track it down to specific INSERTs that are generating the duplicate keys? And verify the constraint it is violating is supposed to be there? If you generate scripts without the data, do they run without error on SQL2005? – SqlACID Apr 09 '10 at 16:48
  • If after trying the script without the data (and if it doesn't work), you might try removing sections of the script and try to run that. I.e. just try run the script that generates the tables only, remove the SQL pertaining to Foreign Keys etc. If you are lucky it may just be a column type or something. – Seanchán Torpéist Apr 09 '10 at 19:01

2 Answers2

1

The problem was that I was trying to import to SQL 2005 Express. I switched to SQL 2005 Standard, and I was able to run the script without error.

Deane
  • 247
  • 3
  • 9
  • It won't let me accept my own answer yet ("...in two days"), but this is the valid answer. – Deane Apr 09 '10 at 19:41
0

I don't know if this is builtin these days to SQL Server 2008 Management Studio, but in 2005 you got more options when scripting if you used a separate download, the SQL Server Database Publishing Wizard Get it here: Microsoft Download Center

That be of some use to you.

Seanchán Torpéist
  • 1,868
  • 2
  • 14
  • 10
  • I tried this, but it would not connect to SQL2008. I think that this was a separate project with SQL2005, and they built it into SQL2008. – Deane Apr 09 '10 at 16:20