Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

SQL Server Database Cloning Tool using C#

Rate me:
Please Sign up or sign in to vote.
4.74/5 (31 votes)
25 May 2015CPOL4 min read 52K   6.5K   61   18
This tool helps in cloning/copying the database from a source environment/server to a target server (usually developers can use it for setting up local DB with production code)

Introduction

I have worked on a couple of big projects that deal with SQL Server as a backend system. As a developer I know the importance of local database. We will have full privileges to play with, run debuggers, profilers and tuning advisers on it. During the initial days of development, it is very easy to take a backup from any of the higher environments and restore it in local. But once it goes to production the size of database becomes huge and it is difficult to restore it in local (our local machines doesn't have terabytes of hard disk right :-))

So how to deal with this situation? How to sync our local database with latest production code base without struggling with size constraints?

There are a couple of tools that help us achieve this, but I thought of creating my own tool. After googling for some time, I found a way to copy the database using SMO (SQL Server Management Objects) API.

Let's see how this tool works and it's implementation.

Using the Tool

Using this tool is so simple. Once you install this tool using attached click once deployment setup file you will see "SqlDbCloner" short cut on you desktop. Double click on it to run the tool. The first window will facilitate the end user to provide the source and target DB connection strings.

Then select the type of activity:

  1. Copy Schema
  2. Copy Data

Image 1

1. Copy Schema

Click on Next.

Then it will pull the list of SQL objects from source connection and provides the end user to select the required objects to copy to target DB.

Bottom of the same window, there are two options

  1. Copy keys and Indexes: Allows to copy all primary keys, indexes, foreign keys and check constraints from source to target.
  2. Drop if exists: This overrites the target object if exists

Image 2

Click on Next. You will be shown a preview of all selected objects.

Image 3

Click on Copy to start copying. If any error while copying specific object, then the error message will be shown in respective row and Error column

Image 4

2. Copy Data

Click on Next.

We will get an editable grid to enter a list of table names to copy the data from source to target DB. If we want all records to be copied, then leave SqlCommand value as empty. If we want specific records to be copied, then provide SELECT querye with appropriate WHERE condition.

This part of tool is developed to help developers to copy master tables / configuration tables data from production to local DB.

Image 5

Once ready, click on Copy to start copying the data. If any error while copying the data (like failed to insert due to primary key constraint violation or object not found) we can see that in respective objects Error field.

Image 6

So simple right :-)

Using the code

There are two important classes and two DTO classes that place key role in this tool.

They are

  1. SqlTranfer
  2. DataTransfer
  3. SqlObject
  4. DataObject

I am not going to go in depth on how I invoked the above core classes and rendered the output to UI. I would like to briefly discuss the core part of the tool. For example, how I am able to copy schema and selected data from the source DB to the destination DB.

Below are just DTO classes to hold the list of objects (tables, stored procs, functions etc...) during the runtime

C#
using Microsoft.SqlServer.Management.Smo;
using System.ComponentModel;
using System.Drawing;

namespace SqlDbCloner.Core.Schema
{
    public class SqlObject
    {
        public Bitmap Status { get; set; } 
        public string Name { get; set; }
        [Browsable(false)]
        public NamedSmoObject Object { get; set; }
        public string Type { get; set; }        
        public string Error { get; set; }

        public SqlObject()
        {
            //Status = Properties.Resources.unknown;
        }       
        
    }
}
C#
using System.Drawing;

namespace SqlDbCloner.Core.Data
{
    public class DataObject
    {
        public Bitmap Status { get; set; }
        public string Table { get; set; }
        public string SqlCommand { get; set; }
        public string Error { get; set; }

        public DataObject()
        {
            //Status = Properties.Resources.unknown;
        }
    }
}

Now lets start with copying schema.

Here is the SqlTransfer class code.

  • In constructor, I am initializing the required objects like connections, server, database and transfer objects of SMO
  • In "InitServer" method, I am setting up the required properties of SMO objects when they initialized. If we dont set them, then SMO will be very slow in getting those information on demand for each object
  • When end user chose schema copy option, will try to connect to source DB and retrieve all SQL objects by invoking "GetSqlObjects" method.
  • Finally will loop through selected objects and call "CreateObject" or "DropAndCreateObject" to create objects in destination database.
  • Except keys and indexes all other objects follow above approach.

At last we call the bellow methods in order to apply keys and indexes on destination databse tables

  1. ApplyIndexes
  2. ApplyForeignKeys
  3. ApplyChecks
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace SqlDbCloner.Core.Schema
{
    public class SqlTransfer
    {
        public string _sourceConnectionString;
        public string _destinationConnectionString;
        public Database sourceDatabase;
        public Database destinationDatabase;
        public Server sourceServer;
        public Server destinationServer;
        public Transfer transfer;
        public ServerConnection sourceConnection;
        public ServerConnection destinationConnection;
        public List<SqlObject> SourceObjects;
        public List<SqlObject> DestinationObjects;

        public SqlTransfer(string src, string dst)
        {
            _sourceConnectionString = src;
            _destinationConnectionString = dst;

            sourceConnection = new ServerConnection(new SqlConnection(_sourceConnectionString));
            sourceServer = new Server(sourceConnection);

            destinationConnection = new ServerConnection(new SqlConnection(_destinationConnectionString));
            destinationServer = new Server(destinationConnection);

            InitServer(sourceServer);
            InitServer(destinationServer);

            sourceDatabase = sourceServer.Databases[sourceServer.ConnectionContext.DatabaseName];
            destinationDatabase = destinationServer.Databases[destinationServer.ConnectionContext.DatabaseName];

            transfer = new Transfer(sourceDatabase);

            transfer.DestinationServer = destinationConnection.ServerInstance;
            transfer.DestinationDatabase = destinationConnection.DatabaseName;
            transfer.DestinationLogin = destinationConnection.Login;
            transfer.DestinationPassword = destinationConnection.Password;

            transfer.Options.ContinueScriptingOnError = true;
            transfer.Options.NoFileGroup = true;
            transfer.Options.NoExecuteAs = true;
            transfer.Options.WithDependencies = false;
            transfer.Options.DriDefaults = true;
            transfer.CopySchema = true;
            transfer.CopyData = false;
            transfer.DropDestinationObjectsFirst = false;

            SourceObjects = GetSqlObjects(sourceDatabase);
            DestinationObjects = GetSqlObjects(destinationDatabase);
        }

        private void InitServer(Server serv)
        {
            // set the default properties we want upon partial instantiation - 
            // smo is *really* slow if you don't do this
            serv.SetDefaultInitFields(typeof(Table), "IsSystemObject", "Name");
            serv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject", "Name");
            serv.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject", "Name");
            serv.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject", "Name");
            serv.SetDefaultInitFields(typeof(Column), "Identity");
            serv.SetDefaultInitFields(typeof(Index), "IndexKeyType");
        }

        private void ResetTransfer()
        {
            transfer.CopyAllDatabaseTriggers = false;
            transfer.CopyAllDefaults = false;
            transfer.CopyAllLogins = false;
            transfer.CopyAllObjects = false;
            transfer.CopyAllPartitionFunctions = false;
            transfer.CopyAllPartitionSchemes = false;
            transfer.CopyAllRoles = false;
            transfer.CopyAllRules = false;
            transfer.CopyAllSchemas = false;
            transfer.CopyAllSqlAssemblies = false;
            transfer.CopyAllStoredProcedures = false;
            transfer.CopyAllSynonyms = false;
            transfer.CopyAllTables = false;
            transfer.CopyAllUserDefinedAggregates = false;
            transfer.CopyAllUserDefinedDataTypes = false;
            transfer.CopyAllUserDefinedFunctions = false;
            transfer.CopyAllUserDefinedTypes = false;
            transfer.CopyAllUsers = false;
            transfer.CopyAllViews = false;
            transfer.CopyAllXmlSchemaCollections = false;
            transfer.CreateTargetDatabase = false;
            //transfer.DropDestinationObjectsFirst = false;
            transfer.PrefetchObjects = false;
            transfer.SourceTranslateChar = false;
        }

        public void DropAndCreateObject(NamedSmoObject obj)
        {
            ResetTransfer();
            transfer.ObjectList.Clear();
            transfer.ObjectList.Add(obj);
            if (DestinationObjects.Any(d => d.Name == obj.Name))
            {                
                transfer.Options.ScriptDrops = true;
                foreach (var script in transfer.ScriptTransfer())
                    (new SqlCommand(script, destinationConnection.SqlConnectionObject)).ExecuteNonQuery();                
            }
            transfer.Options.ScriptDrops = false;
            foreach (var script in transfer.ScriptTransfer())
                (new SqlCommand(script, destinationConnection.SqlConnectionObject)).ExecuteNonQuery();
        }

        public void CreateObject(NamedSmoObject obj)
        {
            ResetTransfer();
            transfer.ObjectList.Clear();
            transfer.ObjectList.Add(obj);
            foreach (var script in transfer.ScriptTransfer())
                (new SqlCommand(script, destinationConnection.SqlConnectionObject)).ExecuteNonQuery();
        }

        private List<SqlObject> GetSqlObjects(Database db)
        {
            List<SqlObject> items = new List<SqlObject>();

            foreach(SqlAssembly item in db.Assemblies)
            {
                if(!item.IsSystemObject)
                    items.Add(new SqlObject { Name = item.Name, Object = item, Type = item.GetType().Name });
            }

            foreach (UserDefinedDataType item in db.UserDefinedDataTypes)
            {
                items.Add(new SqlObject { Name = item.Name, Object = item, Type = item.GetType().Name });
            }

            foreach (UserDefinedTableType item in db.UserDefinedTableTypes)
            {
                items.Add(new SqlObject { Name = item.Name, Object = item, Type = item.GetType().Name });
            }

            foreach (Table item in db.Tables)
            {
                if (!item.IsSystemObject)
                items.Add(new SqlObject { Name = item.Name, Object = item, Type = item.GetType().Name });
            }

            foreach (Microsoft.SqlServer.Management.Smo.View item in db.Views)
            {
                if (!item.IsSystemObject)
                items.Add(new SqlObject { Name = item.Name, Object = item, Type = item.GetType().Name });
            }

            foreach (UserDefinedFunction item in db.UserDefinedFunctions)
            {
                if (!item.IsSystemObject)
                items.Add(new SqlObject { Name = item.Name, Object = item, Type = item.GetType().Name });
            }

            foreach (StoredProcedure item in db.StoredProcedures)
            {
                if (!item.IsSystemObject)
                items.Add(new SqlObject { Name = item.Name, Object = item, Type = item.GetType().Name });
            }

            foreach (Microsoft.SqlServer.Management.Smo.DatabaseDdlTrigger item in db.Triggers)
            {
                if (!item.IsSystemObject)
                items.Add(new SqlObject { Name = item.Name, Object = item, Type = item.GetType().Name });
            }

            return items;
        }

        internal void ApplyIndexes(NamedSmoObject sTable)
        {
            var dTable = destinationDatabase.Tables[sTable.Name];
            foreach (Index srcind in (sTable as Table).Indexes)
            {
                try
                {
                    string name = srcind.Name;
                    Index index = new Index(dTable, name);

                    index.IndexKeyType = srcind.IndexKeyType;
                    index.IsClustered = srcind.IsClustered;
                    index.IsUnique = srcind.IsUnique;
                    index.CompactLargeObjects = srcind.CompactLargeObjects;
                    index.IgnoreDuplicateKeys = srcind.IgnoreDuplicateKeys;
                    index.IsFullTextKey = srcind.IsFullTextKey;
                    index.PadIndex = srcind.PadIndex;
                    index.FileGroup = srcind.FileGroup;

                    foreach (IndexedColumn srccol in srcind.IndexedColumns)
                    {
                        IndexedColumn column =
                         new IndexedColumn(index, srccol.Name, srccol.Descending);
                        column.IsIncluded = srccol.IsIncluded;
                        index.IndexedColumns.Add(column);
                    }

                    index.FileGroup = dTable.FileGroup ?? index.FileGroup;
                    index.Create();
                }
                catch (Exception exc)
                {
                    // Not yet handled
                }
            }
        }

        internal void ApplyForeignKeys(NamedSmoObject sTable)
        {
            var dTable = destinationDatabase.Tables[sTable.Name];
            foreach (ForeignKey sourcefk in (sTable as Table).ForeignKeys)
            {
                try
                {
                    string name = sourcefk.Name;
                    ForeignKey foreignkey = new ForeignKey(dTable, name);
                    foreignkey.DeleteAction = sourcefk.DeleteAction;
                    foreignkey.IsChecked = sourcefk.IsChecked;
                    foreignkey.IsEnabled = sourcefk.IsEnabled;
                    foreignkey.ReferencedTable = sourcefk.ReferencedTable;
                    foreignkey.ReferencedTableSchema = sourcefk.ReferencedTableSchema;
                    foreignkey.UpdateAction = sourcefk.UpdateAction;

                    foreach (ForeignKeyColumn scol in sourcefk.Columns)
                    {
                        string refcol = scol.ReferencedColumn;
                        ForeignKeyColumn column =
                         new ForeignKeyColumn(foreignkey, scol.Name, refcol);
                        foreignkey.Columns.Add(column);
                    }

                    foreignkey.Create();
                }
                catch (Exception exc)
                {
                    // Not yet handled
                }
            }
        }

        internal void ApplyChecks(NamedSmoObject sTable)
        {
            var dTable = destinationDatabase.Tables[sTable.Name];
            foreach (Check chkConstr in (sTable as Table).Checks)
            {
                try
                {
                    Check check = new Check(dTable, chkConstr.Name);
                    check.IsChecked = chkConstr.IsChecked;
                    check.IsEnabled = chkConstr.IsEnabled;
                    check.Text = chkConstr.Text;
                    check.Create();
                }
                catch (Exception exc)
                {
                    // Not yet handled
                }
            }
        }

        internal void Refresh()
        {
            SourceObjects = GetSqlObjects(sourceDatabase);
            DestinationObjects = GetSqlObjects(destinationDatabase);
        }
    }    
}

Now its time to look at copying data

Here we use SqlBulkCopy utility class in System.Data.SqlClient namespace.

  • In constructor, initialize all objects and retrieve all table names in source database.
  • Based on the selected list, loop through each one and call "TransferData" method

By default, for each table "SELECT *" gets executed as query until the end user provide their own query with WHERE clause to copy only selected rows.

C#
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace SqlDbCloner.Core.Data
{
    public class DataTransfer
    {
        public ServerConnection sourceConnection;
        SqlBulkCopy bulkCopy;
        public List<DataObject> SourceObjects;

        public DataTransfer(string src, string dest)
        {
            bulkCopy = new SqlBulkCopy(dest);
            bulkCopy.BatchSize = 500;
            bulkCopy.NotifyAfter = 1000;

            sourceConnection = new ServerConnection(new SqlConnection(src));
            var sourceServer = new Server(sourceConnection);
            InitServer(sourceServer);
            var db = sourceServer.Databases[sourceServer.ConnectionContext.DatabaseName];

            SourceObjects = new List<DataObject>();
            foreach (Table item in db.Tables)
            {
                if (!item.IsSystemObject)
                    SourceObjects.Add(new DataObject { Table = item.Name });
            }
        }

        private void InitServer(Server serv)
        {
            // set the default properties we want upon partial instantiation - 
            // smo is *really* slow if you don't do this
            serv.SetDefaultInitFields(typeof(Table), "IsSystemObject", "Name");
            serv.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject", "Name");
            serv.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject", "Name");
            serv.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject", "Name");
            serv.SetDefaultInitFields(typeof(Column), "Identity");
            serv.SetDefaultInitFields(typeof(Index), "IndexKeyType");
        }

        public void TrasnferData(string table, string query)
        {
            SqlDataReader reader = null;
            SqlCommand myCommand;

            try
            {
                myCommand = new SqlCommand(query, sourceConnection.SqlConnectionObject);
                reader = myCommand.ExecuteReader();

                bulkCopy.DestinationTableName = table;
                bulkCopy.WriteToServer(reader);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }
    }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India
Working as a Senior Developer in an MNC with 3+ years of experience in .Net related technologies. Passionate about programming and software architecture.

Comments and Discussions

 
QuestionVery Import Pin
mohammed227-Oct-23 23:02
mohammed227-Oct-23 23:02 
PraiseNice Work Pin
Member 331697515-Oct-22 10:54
Member 331697515-Oct-22 10:54 
QuestionUpgraded version of SQL Cloning tool Pin
gallargit19-Sep-21 1:16
gallargit19-Sep-21 1:16 
QuestionHow to drop constraints first and then DropAndCreate? Pin
pankaj803875-May-20 3:22
pankaj803875-May-20 3:22 
Questiontrouble whit odbc Pin
Sebastian_b5-Dec-18 4:30
Sebastian_b5-Dec-18 4:30 
BugPrimary Keys and Foreign Keys are missing after creating the schema Pin
Asad Naeem17-Aug-18 7:25
professionalAsad Naeem17-Aug-18 7:25 
GeneralRe: Primary Keys and Foreign Keys are missing after creating the schema Pin
Dan1956a12-Oct-18 17:19
Dan1956a12-Oct-18 17:19 
GeneralRe: Primary Keys and Foreign Keys are missing after creating the schema Pin
David Pierson15-Sep-21 19:39
David Pierson15-Sep-21 19:39 
I also got the "An error occurred while signing" error.

Right-click on the Project (not the solution),
Properties,
go to the Signing tab,
untick the "Sign the ClickOnce manifests".
BugBugs... Pin
Asad Naeem29-May-18 22:29
professionalAsad Naeem29-May-18 22:29 
QuestionCopy Data Pin
Member 126864469-May-17 12:49
Member 126864469-May-17 12:49 
QuestionError when copying data Pin
Vijay Swaroop1-Dec-16 0:51
Vijay Swaroop1-Dec-16 0:51 
AnswerRe: Error when copying data Pin
Vijay Swaroop1-Dec-16 0:58
Vijay Swaroop1-Dec-16 0:58 
GeneralRe: Error when copying data Pin
karan Jagota9-Mar-18 5:47
karan Jagota9-Mar-18 5:47 
GeneralRe: Error when copying data Pin
Asad Naeem30-May-18 19:40
professionalAsad Naeem30-May-18 19:40 
QuestionBest Database Article of May 2015 Pin
Sibeesh Passion10-Jun-15 19:20
professionalSibeesh Passion10-Jun-15 19:20 
QuestionThank you Pin
Member 302085129-May-15 20:38
Member 302085129-May-15 20:38 
SuggestionGreat job; but I have two remarks (one concerning a bug) Pin
Member 1150234825-May-15 22:33
Member 1150234825-May-15 22:33 
GeneralRe: Great job; but I have two remarks (one concerning a bug) Pin
Acharya Raja Sekhar25-May-15 23:32
Acharya Raja Sekhar25-May-15 23:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.