Click here to Skip to main content
Click here to Skip to main content

SMO Tutorial 4 of n - Transferring Data and Tracing

, 22 Jan 2012
Rate this:
Please Sign up or sign in to vote.
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.

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.

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)

About the Author

Kanasz Robert
Architect The Staffing Edge & 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 PinmemberMember 87144215-Dec-13 10:33 
GeneralGreat work Pinmembert.alkahtiri26-Oct-13 3:22 
GeneralRe: Great work PinmvpKanasz Robert7-Jan-14 21:39 
QuestionNice!!! Pinmemberstrucker_luc18-Nov-12 3:16 
AnswerRe: Nice!!! PinmvpKanasz Robert18-Nov-12 3:21 
Questionsqldmo script to sql smo PinmemberMember 16165427-Nov-12 6:02 
QuestionInteresting article and very helpful Pinmemberkr1234564-Nov-12 3:57 
AnswerRe: Interesting article and very helpful PinmvpKanasz Robert4-Nov-12 4:04 
Questionnice PinmembersuperdevX151-Nov-12 6:51 
AnswerRe: nice PinmvpKanasz Robert1-Nov-12 6:55 
Questionvery well written article Pinmemberhakon12331-Oct-12 5:32 
AnswerRe: very well written article PinmvpKanasz Robert31-Oct-12 5:37 
Question5 Pinmembermemlon mulas29-Oct-12 5:15 
nice
AnswerRe: 5 PinmvpKanasz Robert29-Oct-12 5:19 
Questiongood and well written article Pinmemberjackhoal27-Oct-12 3:56 
AnswerRe: good and well written article PinmvpKanasz Robert27-Oct-12 4:00 
Questionhelpful Pinmemberrobkaan27-Oct-12 3:28 
AnswerRe: helpful PinmvpKanasz Robert27-Oct-12 3:31 
Questiongreat Pinmemberwindevvv21-Oct-12 6:51 
AnswerRe: great PinmvpKanasz Robert21-Oct-12 7:00 
QuestionGood Pinmemberkaslaninovic2-Oct-12 22:51 
AnswerRe: Good PinmvpKanasz Robert3-Oct-12 6:49 
Questiongood Pinmemberdeveloper88123-Sep-12 3:04 
AnswerRe: good PinmvpKanasz Robert23-Sep-12 23:12 
Questiongood one Pinmemberbikerius19-Sep-12 2:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 22 Jan 2012
Article Copyright 2012 by Kanasz Robert
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid