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...");

            db.Script(dbso);

            //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);
                    t.Script(tso);
                }
                Console.WriteLine();
            }

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

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

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

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