Transfer SQL Server database schema objects and data to another server or database with SMO

In this tip, I will take you a step ahead and will discuss the Transfer class, which is used to programmatically transfer database objects and data from a database/server to another database/server.

In this code block, I am creating two instances of a Server class; one represents the source server whereas the other one represents the destination server. While connecting to these server you can either use Windows authentication or SQL Server authentication. Next I am taking a reference of AdventureWorks as the source database and creating AdventuresWorkNew database on the specified destination server to copy database objects to.

C# Code Block 1 - Setting up environment

Server mySourceServer = new Server(@"ARSHADALI-PC\ARSHADALI");
Server myDestinationServer = new Server(@"ARSHADALI-LAP\ARSHADALI");
//Using windows authentication
mySourceServer.ConnectionContext.LoginSecure = true;
//Using SQL Server authentication
//mySourceServer.ConnectionContext.LoginSecure = false; //mySourceServer.ConnectionContext.Login = "SQLLogin";
//mySourceServer.ConnectionContext.Password = "entry@2008";
mySourceServer.ConnectionContext.Connect();
//Using windows authentication
myDestinationServer.ConnectionContext.LoginSecure = true;
//Using SQL Server authentication
//myDestinationServer.ConnectionContext.LoginSecure = false;
//myDestinationServer.ConnectionContext.Login = "SQLLogin";
//myDestinationServer.ConnectionContext.Password = "entry@2008";
myDestinationServer.ConnectionContext.Connect();
Database dbSourceAW = mySourceServer.Databases["AdventureWorks"];
Database dbDestinationAW = new Database(myDestinationServer, "AdventureWorksNew");
// Create method will create the database on the specified server
dbDestinationAW.Create();
Console.WriteLine("Database [{0}] created at [{1}] server.", dbDestinationAW.Name, myDestinationServer.Name);

In this script I am creating an instance of the Transfer class and passing an instance of he source database to the constructor, by default CopyAllObjects, CopyData, CopySchema properties are set to true. If you want to copy selected objects then you need to set them to false and set the specific property to true.

For example if you wan to copy all tables then you need to set CopyAllTables property to true, likewise if you want to copy all stored procedures then you need to set CopyAllStoredProcedures to true. Next you need to specify the destination server and database name and finally call the TransferData method to actually start the transfer process.

C# Code Block 2 - SMO Transfer class

//Create an object of Transfer class and pass
//reference of source database to its construtor
Transfer trsfrDB = new Transfer(dbSourceAW);
trsfrDB.CopyAllObjects = false;
trsfrDB.CopyAllSchemas = true;
//Copy all user defined data types from source to destination
trsfrDB.CopyAllUserDefinedDataTypes = true;
//Copy all tables from source to destination
trsfrDB.CopyAllTables = true;
//Copy data of all source tables to destination tables
//It actually generates INSERT statement for destination
trsfrDB.CopyData = true;
//Copy all stored procedure from source to destination
trsfrDB.CopyAllStoredProcedures = true;
//specify the destination server name
trsfrDB.DestinationServer = myDestinationServer.Name;
//specify the destination database name
trsfrDB.DestinationDatabase = dbDestinationAW.Name;
//TransferData method transfers the schema objects and data
//whatever you have specified to destination database
trsfrDB.TransferData();

In code block 2, you learned to transfer database schema objects and data immediately to a destination server and database by calling TransferData method. But there are times, when you would need to generate a script for some later execution. For example you would need to generate database objects and data scripts which you can use later on another server or database to create objects and push data in instead of immediately transferring objects and data to destination. This is what you can achieve with this code. You can use the ScriptingOptions class for different scripting options and finally call EnumScriptTransfer method to generate the scripts. This method returns a string collection which you can loop through to get each individual script by using a foreach statement.

Transfer class has four different events which are raised during its execution cycle. In this code I am wiring up these events with event handlers and also providing a skeleton of these event handlers, you can further customize these handlers per your need. As the name indicates DiscoveryProgress event reports the progress of the discovery process, likewise DataTransferEvent event reports what data has been transferred.

C# Code Block 4 - Events of Transfer class

// wire up event handler to monitor progress
trsfrDB.DataTransferEvent += new DataTransferEventHandler(DataTransferReport);
trsfrDB.DiscoveryProgress += new ProgressReportEventHandler(DiscoveryProgressReport);
trsfrDB.ScriptingProgress += new ProgressReportEventHandler(ScriptingProgressReport);
trsfrDB.ScriptingError += new ScriptingErrorEventHandler(ScriptingErrorReport);

protected static void DataTransferReport(object sender, DataTransferEventArgs args)
{
Console.ForegroundColor = ConsoleColor.Green;
Console.Write("[" + args.DataTransferEventType + "] ");
Console.ResetColor();
Console.WriteLine(" : " + args.Message);
}
protected static void DiscoveryProgressReport(object sender, ProgressReportEventArgs args)
{
Console.WriteLine("[" + args.Current.Value + "]" );
}
protected static void ScriptingProgressReport(object sender, ProgressReportEventArgs args)
{
Console.WriteLine("[" + args.Current.Value + "]");
}
protected static void ScriptingErrorReport(object sender, ScriptingErrorEventArgs args)
{
Console.WriteLine("[" + args.Current.Value + "]");
}

The complete code listing created with SQL Server 2008 and Visual Studio 2008 is below. This code should also work with SQL Server 2005 and Visual Studio 2005.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.Specialized;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
namespace TransferDatabaseSchemaAndData
{
    class Program
    {
        static void Main(string[] args)
        {
            Server mySourceServer = new Server(@"ARSHADALI-PC\ARSHADALI");
            Server myDestinationServer = new Server(@"ARSHADALI-LAP\ARSHADALI");
            try
            {
                //Using windows authentication
                mySourceServer.ConnectionContext.LoginSecure = true;
                //Using SQL Server authentication
                //mySourceServer.ConnectionContext.LoginSecure = false;
                //mySourceServer.ConnectionContext.Login = "SQLLogin";
                //mySourceServer.ConnectionContext.Password = "entry@2008";
                mySourceServer.ConnectionContext.Connect();
                //Using windows authentication
                myDestinationServer.ConnectionContext.LoginSecure = true;
                //Using SQL Server authentication
                //myDestinationServer.ConnectionContext.LoginSecure = false;
                //myDestinationServer.ConnectionContext.Login = "SQLLogin";
                //myDestinationServer.ConnectionContext.Password = "entry@2008";
                myDestinationServer.ConnectionContext.Connect();
                
                Database dbSourceAW = mySourceServer.Databases["AdventureWorks"];
                Database dbDestinationAW = new Database(myDestinationServer, "AdventureWorksNew");
                // Create method will create the database on the specified server
                //dbDestinationAW.Create();
                Console.WriteLine("Database [{0}] created at [{1}] server.", dbDestinationAW.Name, myDestinationServer.Name);
                // Create an object of Transfer class and pass
                //reference of source database to its construtor
                Transfer trsfrDB = new Transfer(dbSourceAW);
                trsfrDB.CopyAllObjects = false;
                trsfrDB.CopyAllSchemas = true;
                //Copy all user defined data types from source to destination
                trsfrDB.CopyAllUserDefinedDataTypes = true;
                //Copy all tables from source to destination
                trsfrDB.CopyAllTables = true;
                //Copy data of all source tables to destination tables
                //It actually generates INSERT statement for destination
                trsfrDB.CopyData = true;
                //Copy all stored procedure from source to destination
                trsfrDB.CopyAllStoredProcedures = true;
                //specify the destination server name
                trsfrDB.DestinationServer = myDestinationServer.Name;
                //specify the destination database name
                trsfrDB.DestinationDatabase = dbDestinationAW.Name;
                //TransferData method transfers the schema objects and data
                //whatever you have specified to destination database
                trsfrDB.TemporaryPackageDirectory = @"D:\TransferDatabaseSchemaAndData\";
                trsfrDB.TransferData();
                //// wire up event handler to monitor progress
                //trsfrDB.DataTransferEvent += new DataTransferEventHandler(DataTransferReport);
                //trsfrDB.DiscoveryProgress += new ProgressReportEventHandler(DiscoveryProgressReport);
                //trsfrDB.ScriptingProgress += new ProgressReportEventHandler(ScriptingProgressReport);
                //trsfrDB.ScriptingError += new ScriptingErrorEventHandler(ScriptingErrorReport);

///* With ScriptingOptions you can specify different scripting
                // * options, for example to include IF NOT EXISTS, DROP
                // * statements, output location etc*/
                //ScriptingOptions scriptOptions = new ScriptingOptions();
                ////scriptOptions.ScriptDrops = true;
                ////scriptOptions.IncludeIfNotExists = true;
                ////scriptOptions.ScriptData = true;
                ////scriptOptions.WithDependencies = true;
                ////scriptOptions.Indexes = true;
                //scriptOptions.FileName = @"D:\TransferDatabaseSchemaAndData.sql";
                //trsfrDB.Options = scriptOptions;
                //IEnumerable<string> scripts = trsfrDB.EnumScriptTransfer();
                //foreach (string script in scripts)
                //    Console.WriteLine(script);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (mySourceServer.ConnectionContext.IsOpen)
                    mySourceServer.ConnectionContext.Disconnect();
                Console.ReadKey();
            }
        }
        protected static void DataTransferReport(object sender, DataTransferEventArgs args)
        {
            Console.ForegroundColor = ConsoleColor.Green;
            Console.Write("[" + args.DataTransferEventType + "] ");
            Console.ResetColor();
            Console.WriteLine(" : " + args.Message);
        }
        protected static void DiscoveryProgressReport(object sender, ProgressReportEventArgs args)
        {
            Console.WriteLine("[" + args.Current.Value + "]" );
        }
        protected static void ScriptingProgressReport(object sender, ProgressReportEventArgs args)
        {
            Console.WriteLine("[" + args.Current.Value + "]");
        }
        protected static void ScriptingErrorReport(object sender, ScriptingErrorEventArgs args)
        {
            Console.WriteLine("[" + args.Current.Value + "]");
        }
    }
}