Transfer SQL Server database schema objects and data with SMO

Problem

Have you wondered how Database Copy Wizard works behind the scenes? Do you have a requirement to create a copy of your database (say copy of your production database for development or testing) programmatically? In this tip, I am going to show you how you can use SMO (SQL Server Management Objects) classes to transfer database objects and data to another server or database.

Solution

In my previous tips on SMO, Getting started with SQL Server Management Objects (SMO), I discussed how you can get started with SMO and how you can programmatically manage a SQL Server instance with your choice of programming language. In tip, Generate Scripts for database objects with SMO for SQL Server I discussed how you can generate SQL object scripts programmatically and in Backup and Restore SQL Server databases programmatically with SMO I discussed how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them programmatically using 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.

C# Code Block 1 – Setting up environment

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 2 – SMO Transfer Class

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 3 – Generating scripts for database objects and data

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.

C# Code Block 4 – Events of Transfer class

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.

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 + "]");
}
}
}

Notes

• Before you start writing your code using SMO, you need to take reference of several assemblies which contains different namespaces to work with SMO. For more details on what these assemblies are and how to reference them in your code, refer to my tip Getting started with SQL Server Management Objects (SMO).
• Location of assemblies in SQL Server 2005 is C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder.
• Location of assemblies in SQL Server 2008 is C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder.
• In SQL Server 2005, the Transfer class is available under Microsoft.SqlServer.Management.Smo namespace and in Microsoft.SqlServer.Smo (microsoft.sqlserver.smo.dll) assembly.
• In SQL Server 2008, the Transfer class is available under Microsoft.SqlServer.Management.Smo namespace and in Microsoft.SqlServer.SmoExtended (microsoft.sqlserver.smoextended.dll) assembly.
• To transfer data, the Transfer class creates a SSIS package dynamically, you can specify the location of this SSIS package using TemporaryPackageDirectory property of the Transfer class instance.
• If you try to connect SQL Server 2008 from SMO 2005, you will get an exception error “SQL Server <10.0> version is not supported”.

Next Steps

• Download the code
• Review these other SMO tips
  • Getting started with SQL Server Management Objects (SMO).
  • Generate Scripts for database objects with SMO for SQL Server
  • Backup and Restore SQL Server databases programmatically with SMO
• Review Transfer class on MSDN.
• Review all of my previous tips

Arshad Ali

Arshad specializes in Database, Data Warehousing and Business Intelligence application design, development and deployment, at enterprise level, with SQL Server, SSIS, SSRS, SSAS, Service Broker, MDS, DQS, SharePoint and PPS. He is a Microsoft Certified IT Professional in Microsoft SQL Server.  He also has experience developing applications in VB/ASP/.NET/ASP.NET/C#, and is a Microsoft Certified Application Developer and Solution Developer for the .NET platform in Web/Windows/Enterprise.  Arshad has Master’s degrees in Computer Applications and Business Administration in IT.

Disclaimer: Arshad worked for Microsoft and helped people and businesses make better use of technology to realize their full potential. The opinions mentioned in his tips and articles herein are solely his and do not reflect those of his current or previous employers.

• MSSQLTips Awards: Champion (100+ tips) – 2014