can't get EnumScript() to generate constraints

I'm trying to get programmatically what I can get manually from SSMS using Tasks > Generate Scripts

The code below works fine, EXCEPT it doesn't generate any constraints. I don't get any ALTER TABLE [foo] ADD CONSTRAINT ... ON DELETE CASCADE etc etc. I've tried a lot of combinations of Dri options and on different databases as well. I'm stumped.

Thanks for insight!

        Scripter scrp = new Scripter(srv)
        {
            Options =
            {
                ScriptDrops = false,
                WithDependencies = false,
                Indexes = true,
                Triggers = false,
                Default = true,
                DriAll = true,
                //ScriptData = true,
                ScriptSchema = true,
            }
        };

        var urns = new List<Urn>();

        foreach (Table tb in db.Tables)
        {
            if (tb.IsSystemObject == false)
            {
                urns.Add(tb.Urn);
            }
        }

        var inserts = scrp.EnumScript(urns.ToArray());
        File.WriteAllLines(path, inserts);

Well, I found a solution, which is to use the Script method of each object to produce the schema and the EnumScript method (with scriptSchema=false) to produce the inserts for the table content.

        foreach (Table tb in db.Tables)
        {
            if (tb.IsSystemObject == false)
            {
                foreach (var s in tb.Script(schemaOptions))
                    strings.Add(s);

                if (scriptData)
                {
                    foreach (var i in tb.EnumScript(insertOptions))
                        strings.Add(i);
                }
            }
        }

I confess this solution feels a bit hollow because I never found out why the original method didn't work. It's a Repair without a Diagnosis, but a repair nonetheless.

As to why I wrote this thing in the first place, my database is on a shared server and there isn't any way to get an automated backup that I could use offline or somewhere else. So this is my backup scheme.

The solution above follows the code example given by Microsoft here: Scripting . The problem with this approach is the tables are scripted in No Particular Order, but need to be in order of their dependencies in order for the constraints to be defined and for rows to be inserted. Can't reference a foreign key in a table that doesn't exist yet.

The best solution I have so far is to use DependencyWalker.DiscoverDependencies() to get a dependency tree, DependencyWalker.WalkDependencies() to get a linear list and iterate over that list, as follows:

        var urns = new List<Urn>();
        Scripter schemaScripter = new Scripter(srv) { Options = schemaOptions };
        Scripter insertScripter = new Scripter(srv) { Options = insertOptions };
        var dw = new DependencyWalker(srv);

        foreach (Table t in db.Tables)
            if (t.IsSystemObject == false)
                urns.Add(t.Urn);
        DependencyTree dTree = dw.DiscoverDependencies(urns.ToArray(), true);
        DependencyCollection dColl = dw.WalkDependencies(dTree);

        foreach (var d in dColl)
        {
            foreach (var s in schemaScripter.Script(new Urn[] { d.Urn }))
                strings.Add(s);
            strings.Add("GO");
            if (scriptData)
            {
                int n = 0;
                foreach (var i in insertScripter.EnumScript(new Urn[] {d.Urn}))
                {
                    strings.Add(i);
                    if ((++n) % 100 == 0)
                        strings.Add("GO");
                }
            }
        }
        ...
        File.WriteAllLines(path, strings);

Adding a "GO" every so often keeps the batch size small so SSMS doesn't run out of memory.

To complete the example, the database gets scripted thus:

        foreach (var s in db.Script(new ScriptingOptions { ScriptSchema = true }))
            strings.Add(s);
        strings.Add("GO");
        strings.Add("use " + dbName);
        strings.Add("GO");

Users, views, stored procedures are scripted thus:

        foreach (User u in db.Users)
        {
            if (u.IsSystemObject == false)
            {
                foreach (var s in u.Script(new ScriptingOptions { ScriptSchema = true }))
                    strings.Add(s);
            }
        }

The file produced by this code can be used to recreate the database. I have it set up on an old laptop to pull a snapshot of my online database every hour. Poor man's log shipping / backups / mirroring.

Recommended topics

Hot tags