Click here to Skip to main content
15,860,859 members
Articles / Database Development / SQL Server

SMO Tutorial 4 of n - Transferring Data and Tracing

Rate me:
Please Sign up or sign in to vote.
4.88/5 (44 votes)
22 Jan 2012CPOL8 min read 73.6K   4.3K   80   42
In this fourth part of the tutorial, I will show you how transfer objects and catch trace messages using SMO.

Introduction

Last year I have started to write articles about SMO. In the first article, I have described what Server Management Objects are. I have shown how to work with database storage objects. In the second article, I have described how to use classes not related to database storage objects. I third article I have described scripting capabilities of SMO. In this article I will demonstrate how to transfer data between databases and how to trace events.

Transferring Data

Sometimes developers need to transfer data from one database to another. There are many ways how to solve this problem. You can move/copy database, detach database from old server and attach it to new one or back up the database and restore it on new server or you will create your own application that transfer database on it’s new server. In this article I will describe the last mentioned way.

To accomplish this task you need to use Transfer class. This class is a utility class that provides tools to transfer objects.

Objects in the database schema are transferred by executing a generated script on the target server. Table data is transferred with a dynamically created DTS package. This class is in the Microsoft.SqlServer.Management.Smo namespace.

C#
static void Main(string[] args)
{
    ServerConnection conn = new ServerConnection(@"posta","kanasz","chaaron");
    try
    {
        Server server = new Server(conn);
        Database db = server.Databases["ApplicationErrors"];
        Transfer t = new Transfer(db);
                
        t.CopyAllTables= true;
        t.Options.WithDependencies = true;
        t.DestinationServer = @"ANANAS\ANANAS2009";
        t.DestinationLogin = "kanasz";
        t.DestinationPassword = "chaaron";
        t.DestinationDatabase = "ApplicationErrors";
        t.CreateTargetDatabase = true;
        t.CopyData= true;
        t.DataTransferEvent +=new DataTransferEventHandler(DataTransferEvent_Handler);
        t.DiscoveryProgress +=new ProgressReportEventHandler(DiscoveryProgress_Handler);
        t.TransferData();

        Console.WriteLine("Press any key to continue.");
        Console.ReadKey();
    }
    catch (Exception err)
    {
        Console.WriteLine("ERROR:" + err.Message);
    }
}

protected static void DiscoveryProgress_Handler
(object sender, ProgressReportEventArgs e
    )
{
    Console.WriteLine(e.Total + "/" + e.TotalCount + " " + e.Current.Value);
}

protected static void DataTransferEvent_Handler(
    object sender, DataTransferEventArgs e)
{
    Console.WriteLine("[" + e.DataTransferEventType + "] " + e.Message);
}
Transfer04.jpg

Previous example demonstrate how to transfer database from one database server to another database server. 8 properties are set to configure transfer object:

  • CopyAllTables, Gets or sets the Boolean property value that specifies whether all the SQL Server tables are copied from the source database to the target database in the transfer operation.
  • Options, Represents scripting options object that provides programmatic options that can be set for scripting operations. Its property WithDependencies is property values that specifies whether to include all dependent objects in the generated scipt.
  • DestinationServer, Represents the instance of SQL Server that owns the database that is the destination of the transfer operation.
  • DestinationLogin, Gets or sets the logon for the instance of SQL Server to which the destination database belongs.
  • DestinationPassword, Gets or sets the logon password for the instance of SQL Server to which the destination database belongs.
  • DestinationDatabase, Gets or sets the database that is the destination of the transfer operation.
  • CreateTargetDatabase, Gets or sets the Boolean property value that specifies whether the target database is created in the transfer operation.
  • CopyData, Gets or sets the Boolean property value that specifies whether all the data is copied from the source database to the target database in the transfer operation.
Two events are handled:
  • DataTransferEvent, Reports that data has been transferred. Using this event you can get information about currently executed script.
  • DataDiscoveryEvent, Reports the progress of the discovery process.

Another example

Following example demonstrate how to use more options of Transfer class. This example allows you to transfer data from one sql database server to another. Instead of this It allows you to generate transferring script and use more transferring and generating options.

Main form of this example consists of three tabs. In first tab you can choose database servers and set login credentials for them.

Transfer01.jpg

In second part you can choose transferring options:

  • Copy All Db Triggers, Gets or sets the Boolean property value that specifies whether all the SQL Server database triggers are copied from the source database to the target database in the transfer operation.
  • Copy All Defaults, Gets or sets the Boolean property value that specifies whether all the SQL Server defaults are copied from the source database to the target database in the transfer operation.
  • Copy All Full Text Catalogs, Gets or sets the Boolean property value that specifies whether all the SQL Server Full-Text catalogs are copied from the source database to the target database in the transfer operation.
  • Copy All Full Text Stop Lists, Gets or sets the Boolean property value that specifies whether all the SQL Server full-text stop lists are copied from the source database to the target database in the transfer operation.
  • Copy All Logins, Gets or sets the Boolean property value that specifies whether all the SQL Server logins are copied from the source database to the target database in the transfer operation.
  • Copy All Objects, Gets or sets the Boolean property value that specifies whether all the SQL Server objects are copied from the source database to the target database in the transfer operation.
  • Copy Data, Gets or sets the Boolean property value that specifies whether all the data is copied from the source database to the target database in the transfer operation.
  • Copy All Partition Functions, Gets or sets the Boolean property value that specifies whether all the SQL Server partition functions are copied from the source database to the target database in the transfer operation.
  • Copy All Partition Schemes, Gets or sets the Boolean property value that specifies whether all the SQL Server partition schemes are copied from the source database to the target database in the transfer operation.
  • Copy All Plan Guides, Gets or sets the Boolean property value that specifies whether all the SQL Server plan guides are copied from the source database to the target database in the transfer operation.
  • Copy All Roles, Gets or sets the Boolean property value that specifies whether all the SQL Server roles are copied from the source database to the target database in the transfer operation.
  • Copy All Rules, Gets or sets the Boolean property value that specifies whether all the SQL Server rules are copied from the source database to the target database in the transfer operation.
  • Copy All Schemas, Gets or sets the Boolean property value that specifies whether all the SQL Server schemas are copied from the source database to the target database in the transfer operation.
  • Copy All Sql Assemblies, Gets or sets the Boolean property value that specifies whether all the SQL Server assemblies are copied from the source database to the target database in the transfer operation.
  • Copy All SPs, Gets or sets the Boolean property value that specifies whether all the SQL Server stored procedures are copied from the source database to the target database in the transfer operation.
  • Copy All Synonyms, Gets or sets the Boolean property value that specifies whether all the SQL Server synonyms are copied from the source database to the target database in the transfer operation.
  • Copy All Tables, Gets or sets the Boolean property value that specifies whether all the SQL Server tables are copied from the source database to the target database in the transfer operation.
  • Copy All Aggregates, Gets or sets the Boolean property value that specifies whether all the SQL Server user-defined aggregates are copied from the source database to the target database in the transfer operation.
  • Copy All UDDTs, Gets or sets the Boolean property value that specifies whether all the SQL Server user-defined data types are copied from the source database to the target database in the transfer operation.
  • Create Target Database, Gets or sets the Boolean property value that specifies whether the target database is created in the transfer operation.
  • Copy All UDFs, Gets or sets the Boolean property value that specifies whether all the SQL Server user-defined functions are copied from the source database to the target database in the transfer operation.
  • Copy All UDTTs, Gets or sets the Boolean property value that specifies whether all the SQL Server user-defined table types are copied from the source database to the target database in the transfer operation.
  • Copy All UDTs, Gets or sets the Boolean property value that specifies whether all the SQL Server user-defined types are copied from the source database to the target database in the transfer operation.
  • Copy All Users, Gets or sets the Boolean property value that specifies whether all the SQL Server database users are copied from the source database to the target database in the transfer operation.
  • Copy All Views, Gets or sets the Boolean property value that specifies whether all the SQL Server views are copied from the source database to the target database in the transfer operation.
  • Copy All Xml Schema Colls, Gets or sets the Boolean property value that specifies whether all the SQL Server XML schema collections are copied from the source database to the target database in the transfer operation.

Transfer01.jpg

In the last part you can see transferring script. Transfer03.jpg

Transferring Data

SQL Server trace is mechanism for monitoring activity inside SQL Server. Every time the activity occurs, SQL Server generates event and this event is shipped to monitoring tool (SQL Profiles) which displays all information about event. SQL Trace and SQL Profiler allows you to analyze performance and then tune your database up.

When you want to use tracing capabilities of SQL Server you need to user SMO Trace and replay classes which provide an interface with which you can trace and record events, manipulate and analyze data and replay trace events. All trace and replay classes are located in Microsoft.SqlServer.Management.Trace namespace.

The following basic example logs the name of the first 10 events.

C#
static void Main(string[] args)
{
    TraceServer ts = new TraceServer();

    ConnectionInfoBase ci = new SqlConnectionInfo(@"ANANAS\ANANAS2009");
    ((SqlConnectionInfo)ci).UseIntegratedSecurity = true;
    ts.InitializeAsReader(ci,
        @"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\Standard.tdf");

    for (int i = 0; i < ts.FieldCount; i++)
    {
        Console.WriteLine(ts.GetName(i));
    }

    int eventNumber = 0;
    while (ts.Read())
    {
        Console.Write(ts.GetValue(0) + Environment.NewLine);
                
        eventNumber++;
        if (eventNumber == 10)
            break;
    }
    ts.Close();

    Console.WriteLine(Environment.NewLine + "Press any key to continue.");
    Console.ReadKey();
}
Transfer01.jpg

The first step is to create TraceServer object that represents a new trace on an instance of SQL Server. Next step is to create connection to SQL Server. This connection is represented by ConnectionInfoBase object. When this two objects are created, then you must initialize the reader by InitializeAsReader method. This method accepts 2 parameters. Firs of them is ConnectionInfoBase object and the second is trace definition file. The trace definition file determines the information contained in the trace which events and what columns of trace data are captured for each event.

Next more advanced example shows all information about events in grid. You can start and stop tracing wherever you want. This example consists of two screens: Main screen and Login screen. In Login screen you can set SQL Server instance and credentials.

trace03.jpg

Main screen displays all information in gridview. In top panel are three buttons: Login, Select template and Start.

trace02.jpg

History

  • 22 Jan 2012 - Article posted

License

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


Written By
Architect Marwin Cassovia Soft
Slovakia Slovakia
My name is Robert Kanasz and I have been working with ASP.NET, WinForms and C# for several years.
MCSD - Web Applications
MCSE - Data Platform
MCPD - ASP.NET Developer 3.5
- Web Developer 4
MCITP - Database Administrator 2008
- Database Developer 2008
MCSA - SQL Server 2012
MCTS - .NET Framework 3.5, ASP.NET Applications
- SQL Server 2008, Database Development
- SQL Server 2008, Implementation and Maintenance
- .NET Framework 4, Data Access
- .NET Framework 4, Service Communication Applications
- .NET Framework 4, Web Applications
MS - Programming in HTML5 with JavaScript and CSS3 Specialist

Open source projects: DBScripter - Library for scripting SQL Server database objects


Please, do not forget vote

Comments and Discussions

 
Questiontranfer Pin
Member 87144215-Dec-13 10:33
Member 87144215-Dec-13 10:33 
GeneralGreat work Pin
t.alkahtiri26-Oct-13 3:22
t.alkahtiri26-Oct-13 3:22 
GeneralRe: Great work Pin
Kanasz Robert7-Jan-14 21:39
professionalKanasz Robert7-Jan-14 21:39 
QuestionNice!!! Pin
strucker_luc18-Nov-12 3:16
strucker_luc18-Nov-12 3:16 
Very Well!!!
AnswerRe: Nice!!! Pin
Kanasz Robert18-Nov-12 3:21
professionalKanasz Robert18-Nov-12 3:21 
Questionsqldmo script to sql smo Pin
Member 16165427-Nov-12 6:02
Member 16165427-Nov-12 6:02 
QuestionInteresting article and very helpful Pin
kr1234564-Nov-12 3:57
kr1234564-Nov-12 3:57 
AnswerRe: Interesting article and very helpful Pin
Kanasz Robert4-Nov-12 4:04
professionalKanasz Robert4-Nov-12 4:04 
Questionnice Pin
superdevX151-Nov-12 6:51
superdevX151-Nov-12 6:51 
AnswerRe: nice Pin
Kanasz Robert1-Nov-12 6:55
professionalKanasz Robert1-Nov-12 6:55 
Questionvery well written article Pin
hakon12331-Oct-12 5:32
hakon12331-Oct-12 5:32 
AnswerRe: very well written article Pin
Kanasz Robert31-Oct-12 5:37
professionalKanasz Robert31-Oct-12 5:37 
Question5 Pin
memlon mulas29-Oct-12 5:15
memlon mulas29-Oct-12 5:15 
AnswerRe: 5 Pin
Kanasz Robert29-Oct-12 5:19
professionalKanasz Robert29-Oct-12 5:19 
Questiongood and well written article Pin
jackhoal27-Oct-12 3:56
jackhoal27-Oct-12 3:56 
AnswerRe: good and well written article Pin
Kanasz Robert27-Oct-12 4:00
professionalKanasz Robert27-Oct-12 4:00 
Questionhelpful Pin
robkaan27-Oct-12 3:28
robkaan27-Oct-12 3:28 
AnswerRe: helpful Pin
Kanasz Robert27-Oct-12 3:31
professionalKanasz Robert27-Oct-12 3:31 
Questiongreat Pin
windevvv21-Oct-12 6:51
windevvv21-Oct-12 6:51 
AnswerRe: great Pin
Kanasz Robert21-Oct-12 7:00
professionalKanasz Robert21-Oct-12 7:00 
QuestionGood Pin
kaslaninovic2-Oct-12 22:51
kaslaninovic2-Oct-12 22:51 
AnswerRe: Good Pin
Kanasz Robert3-Oct-12 6:49
professionalKanasz Robert3-Oct-12 6:49 
Questiongood Pin
developer88123-Sep-12 3:04
developer88123-Sep-12 3:04 
AnswerRe: good Pin
Kanasz Robert23-Sep-12 23:12
professionalKanasz Robert23-Sep-12 23:12 
Questiongood one Pin
bikerius19-Sep-12 2:10
bikerius19-Sep-12 2:10 

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.