Learn more about SQL Server tools

 
 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Transfer SQL Server database schema objects and data with SMO


By:   |   Read Comments (9)   |   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:





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





More SQL Server Solutions




Post a comment or let the author know this tip helped you.

All comments are reviewed, so stay on subject or we may delete your comment.

*Name    *Email    Notify for updates 

Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Wednesday, January 13, 2016 - 4:06:51 PM - jeremyh Back To Top

Hi Thanks for the really detailed post. I managed to take some of your examples and rewrite them in F# and it works perfectly. You can find the convert to F# code here: http://www.fssnip.net/tM. The handling of events is made very clean with inline lamda functions and property assignments in constructors is helpful too!


Wednesday, October 29, 2014 - 4:56:04 AM - sadat Back To Top

hi

I create the data base,but i want to create it in a specific path.

how i can do this?is there any properties?


Wednesday, August 27, 2014 - 11:59:29 AM - Brian Back To Top

For those of you getting the db creation but no data transfer, might I suggest this change to code block #2.

Change

Transfer trsfrDB = new Transfer(dbSourceAW)

to 

Transfer trsfrDB = new Transfer (mySourceServer.Databases["AdventureWorks"])

 


Tuesday, August 19, 2014 - 7:02:39 AM - Rahul Back To Top

Dear Sir,

Provided code is running good on my local machine but when I connected to live server it created a new database but not aboe to transfer data - Error -

 

Login failed for user 'PURELIFE\Guest'.

 

I am not able to understand why this is happening, As it is able to create new DB but showing error on transferring data.

Works good on local sql server.


Sunday, May 25, 2014 - 1:31:05 PM - Binh Duong Back To Top

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?


Wednesday, January 15, 2014 - 9:42:59 AM - raja Back To Top

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....


Tuesday, November 27, 2012 - 5:57:21 PM - Dilip Singh Dodiya Back To Top

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

 


Thursday, October 18, 2012 - 3:21:12 PM - Nikhil Agrawal Back To Top

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


Wednesday, May 16, 2012 - 7:48:35 AM - stanley Back To Top

Please i need help

i want to synchronize my offline database with the online database

 


Learn more about SQL Server tools