Benjamin Wright-Jones

Exploring data and distributed systems [I also cross-post to]

September, 2008

  • Benjamin Wright-Jones

    SQL Server 2008 CU1 has just been posted


    In case you haven’t seen this…

  • Benjamin Wright-Jones

    Making a database clone using SMO


    I’ve been playing around with SMO a bit more over the past few days as I’m finding it really useful in some situations.  Following on from previous test with SMO and indexes, I was wondering if I could script the database statistics and histograms in SMO rather than use Management Studio.  As a quick reminder, it is possible to make a ‘clone’ of the database statistics and histograms in SQL Server 2005 (with SP2).  This option is buried in the generate scripts window.  A database clone can be very useful when troubleshooting query plans rather than have to rely on a full copy of the database (data+objects+stats).  I actually think using SMO directly in a C# console window is faster than using the SSMS interface but I need to do more testing to validate this. 

    You’ll see below that I generate scripts of the CREATE DATABASE statement, tables plus statistics and histograms, stored procedures, user-defined functions, partition schemes and partition functions, whilst excluding all system objects. 

    using System;
    using System.Collections.Generic;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;
    using System.Text;
    using System.Collections.Specialized;

    namespace SQLSMO
        class SMOClone
            static void Main(string[] args)
                string servername;
                string databasename;

                servername = "servername\\instance";
                databasename = "databasename";

                Server server = new Server(servername);
                Database db = server.Databases[databasename];

                //include the database create syntax
                ScriptingOptions dbso = new ScriptingOptions();
                dbso.FileName = "e:\\" + databasename + "-create.sql";
                dbso.AppendToFile = true;

                Console.WriteLine("Scripting database: " + databasename + ". Please wait...");


                //scripting options
                ScriptingOptions tso = new ScriptingOptions();
                tso.ScriptDrops = false;
                tso.Indexes = true;
                tso.ClusteredIndexes = true;
                tso.PrimaryObject = true;
                tso.SchemaQualify = true;
                tso.NoIndexPartitioningSchemes = false;
                tso.NoFileGroup = false;
                tso.DriPrimaryKey = true;
                tso.DriChecks = true;
                tso.DriAllKeys = true;
                tso.AllowSystemObjects = false;
                tso.IncludeIfNotExists = false;
                tso.DriForeignKeys = true;
                tso.DriAllConstraints = true;
                tso.DriIncludeSystemNames = true;
                tso.AnsiPadding = true;
                tso.IncludeDatabaseContext = false;
                tso.FileName = "e:\\" + databasename + "-clone.sql";
                tso.AppendToFile = true;
                //include statistics and histogram data for db clone
                tso.OptimizerData = true;
                tso.Statistics = true;

                foreach (Table t in db.Tables)
                    if (!t.IsSystemObject)
                        Console.WriteLine("Scripting Table & Statistics: " + t);

                foreach (StoredProcedure sp in db.StoredProcedures)
                    if (!sp.IsSystemObject)
                        Console.WriteLine("Scripting Stored Procedure: " + sp);

                foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
                    if (!udf.IsSystemObject)
                        Console.WriteLine("Scripting Function: " + udf);

                foreach (PartitionFunction pf in db.PartitionFunctions)
                    Console.WriteLine("Scripting Partition Function: " + pf);
                foreach (PartitionScheme ps in db.PartitionSchemes )
                    Console.WriteLine("Scripting Partition Scheme: " + ps);

                Console.Write("Scripting completed. Press any key to exit.");

Page 1 of 1 (2 items)