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

Comments (1)

Comments are closed.

Skip to main content