Getting around cyclical foreign key errors when trying to generate insert data scripts in SQL 2008

Question

I am trying to generate some insert scripts using the SQL Server 2008 Script Wizard. Upon generating the scripts, I get the following error:

"The selected database contains foreign keys that create a cycle. Publishing data only is not supported for databases with cyclical foreign key relationships."

I've attempted to disable and remove all constraints in the database. The error is still occurring. Is there any way to get around this? Possibly make SQL ignore the constraints while generating the scripts.

Answer 1

On the Wizard page where you choose the radio button to select All Database Objects or Specific Objects, make sure to select All Database Objects. For some reason the tool needs something in there to generate even if you just want the table insert script.

Once I changed that radio button to All Database Objects, and selected the Advanced option to generate Type of script = Data Only, it worked all the way through.

Answer 2

I had the same problem as the OP. Then I tried again, this time in the advanced options, for the "types of data to script" option, I selected "schema and data" rather than data only. Then it worked for me without complaining about cyclical keys.

Answer 3

I was having the same issue, and I discovered today that you can use SQL Server Management Studio 2012 against a 2008 R2 DB and you won't get the error:

Sql Server Scripting Data Only: Workaround for CyclicalForeignKeyException?