Generate Scripts - Data only - Cyclic dependencies found error

Question

I'm trying to generate a script of all the data in a database so I can move just the data over to an identical database on another server. In SQL Server 2012 I right click on the source database

Tasks > Generate Scripts > Script entire database and all database objects > Advanced > Types of data to script: data only

This gives me an error something about a Cyclic dependency. If I change the type of data to script to schema AND data it generates fine. How can I fix this to work with data only?

Action Result Getting a list of objects from MyDatabase Failed

Detailed report:

Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> Microsoft.SqlServer.Management.Smo.SmoException: Cyclic dependencies found. at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.DependencyGraphTraversal(Int32 num, Dictionary2 dictionary, List1 sortedList, HashSet1 visited, HashSet1 current) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.SortDictionary(Dictionary2 dictionary) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.SortDataSet(DataSet ds) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.SortDataSet(List1 objectList, Dictionary2 idDictionary, DataSet ds) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ExecuteQueryUsingTempTable(List1 objectList, List1 list, String query) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ResolveTableOnlyDependencies() at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ResolveDependencies() at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.Order(IEnumerable1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List1 urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(Urn[] urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

Answer 1

One of the possible solutions that worked for me is generate scripts as "Schema and data" instead of "Data only". Then I could just remove all unnecessary schema stuff and leave only data. Hope that also helps.

Answer 2

I had this same exact error... See my answer here: http://mattslay.com/ssms-generate-scripts-data-only-cyclic-dependencies-found-error/

Then, on a whim, knowing that I had successfully scripted this database before, I connected to the Sql Server 2012 instance using Sql Server Management 2008 R2, and I was able to generated the scripts without error!!

Note, that I did data-only, and I had to allow it to script all database objects. I tried to select only certain tables, but that gave some other errors that I wasn’t willing to track down, so I just did the whole thing.

Answer 3

I had the same problem, and don't have SSMS 2008 R2 installed to do what Matt suggested. So what I did was changed the advanced settings to have it create the schema and data, and also set it to check if objects already exist. I set most of the other stuff to false (ie Primary Keys, Unique Keys, etc) so that all that's in the sql file is the basic create table statement, and insert statements. Since it's checking if the table doesn't already exist before creating the table, you'll be fine to run the script if the table already exists. So it behaves just like it would if it were just the insert statements. If you're creating a single sql file for several selected tables, it puts all the table creation blocks at the top of the script, so it's easy enough to remove all of them after the file is created if you want.