Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Transfer SQL Server database schema objects and data with SMO

MSSQLTips author Arshad Ali By:   |   Read Comments (5)   |   Related Tips: More > SQL Server Management Objects 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

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

C# Code Block 2

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

C# Code Block 3

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

/* 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);

C# Code Block 4

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.

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



Last Update: 12/29/2009


About the author
MSSQLTips author Arshad Ali
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, May 16, 2012 - 7:48:35 AM - stanley Read The Tip

Please i need help

i want to synchronize my offline database with the online database

 


Thursday, October 18, 2012 - 3:21:12 PM - Nikhil Agrawal Read The Tip

Good Article, learnt a lot about how smo works. I'm trying to transfer a sql database to sql unicode. So the columns need to have - nchar, nvarchar and ntext - change in the schema. How can I use this code listing you have to make my scenario work.

Thanks,

Nikhil


Tuesday, November 27, 2012 - 5:57:21 PM - Dilip Singh Dodiya Read The Tip

Hi Arshad,

Thank you for sharing such great information. I need some input on CDC enabled tables. Actually I have some tables with CDC enabled in source server and need to copy those tables to target server with CDC enabled. Please let me know the way to achieve this.

Thank You,
Dilip

 


Wednesday, January 15, 2014 - 9:42:59 AM - raja Read The Tip

i am unable to copy the database to my local machine ,because my system is under domain.could u help me how to copy the database from server machine to my system....it is getting error that login failed for your domain....


Sunday, May 25, 2014 - 1:31:05 PM - Binh Duong Read The Tip

I tried to use your sample, no exception was thrown BUT no data transfer from my local database to another one (remote database). 2 databases have the same schema, could anyone helps?



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.