Benjamin Wright-Jones

Exploring Information, Infrastructure and Solution Architecture

September, 2008

Posts
  • Benjamin Wright-Jones

    Making a database clone using SMO

    • 1 Comments

    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();
            }
        }
    }

  • Benjamin Wright-Jones

    SQL Server 2008 CU1 has just been posted

    • 1 Comments

    In case you haven’t seen this…http://support.microsoft.com/kb/956717

Page 1 of 1 (2 items)