Click here to Skip to main content
11,583,859 members (44,652 online)
Click here to Skip to main content

SMO Tutorial 4 of n - Transferring Data and Tracing

, 22 Jan 2012 CPOL 34.6K 3.2K 74
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)

Share

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

You may also be interested in...

Comments and Discussions

 
Questiontranfer Pin
Member 87144215-Dec-13 10:33
memberMember 87144215-Dec-13 10:33 
GeneralGreat work Pin
t.alkahtiri26-Oct-13 3:22
membert.alkahtiri26-Oct-13 3:22 
GeneralRe: Great work Pin
Kanasz Robert7-Jan-14 21:39
mvpKanasz Robert7-Jan-14 21:39 
QuestionNice!!! Pin
strucker_luc18-Nov-12 3:16
memberstrucker_luc18-Nov-12 3:16 
AnswerRe: Nice!!! Pin
Kanasz Robert18-Nov-12 3:21
mvpKanasz Robert18-Nov-12 3:21 
Questionsqldmo script to sql smo Pin
Member 16165427-Nov-12 6:02
memberMember 16165427-Nov-12 6:02 
QuestionInteresting article and very helpful Pin
kr1234564-Nov-12 3:57
memberkr1234564-Nov-12 3:57 
AnswerRe: Interesting article and very helpful Pin
Kanasz Robert4-Nov-12 4:04
mvpKanasz Robert4-Nov-12 4:04 
Questionnice Pin
superdevX151-Nov-12 6:51
membersuperdevX151-Nov-12 6:51 
AnswerRe: nice Pin
Kanasz Robert1-Nov-12 6:55
mvpKanasz Robert1-Nov-12 6:55 
Questionvery well written article Pin
hakon12331-Oct-12 5:32
memberhakon12331-Oct-12 5:32 
AnswerRe: very well written article Pin
Kanasz Robert31-Oct-12 5:37
mvpKanasz Robert31-Oct-12 5:37 
Question5 Pin
memlon mulas29-Oct-12 5:15
membermemlon mulas29-Oct-12 5:15 
AnswerRe: 5 Pin
Kanasz Robert29-Oct-12 5:19
mvpKanasz Robert29-Oct-12 5:19 
Questiongood and well written article Pin
jackhoal27-Oct-12 3:56
memberjackhoal27-Oct-12 3:56 
AnswerRe: good and well written article Pin
Kanasz Robert27-Oct-12 4:00
mvpKanasz Robert27-Oct-12 4:00 
Questionhelpful Pin
robkaan27-Oct-12 3:28
memberrobkaan27-Oct-12 3:28 
AnswerRe: helpful Pin
Kanasz Robert27-Oct-12 3:31
mvpKanasz Robert27-Oct-12 3:31 
Questiongreat Pin
windevvv21-Oct-12 6:51
memberwindevvv21-Oct-12 6:51 
AnswerRe: great Pin
Kanasz Robert21-Oct-12 7:00
mvpKanasz Robert21-Oct-12 7:00 
QuestionGood Pin
kaslaninovic2-Oct-12 22:51
memberkaslaninovic2-Oct-12 22:51 
AnswerRe: Good Pin
Kanasz Robert3-Oct-12 6:49
mvpKanasz Robert3-Oct-12 6:49 
Questiongood Pin
developer88123-Sep-12 3:04
memberdeveloper88123-Sep-12 3:04 
5+
AnswerRe: good Pin
Kanasz Robert23-Sep-12 23:12
mvpKanasz Robert23-Sep-12 23:12 
Questiongood one Pin
bikerius19-Sep-12 2:10
memberbikerius19-Sep-12 2:10 
AnswerRe: good one Pin
Kanasz Robert19-Sep-12 4:21
mvpKanasz Robert19-Sep-12 4:21 
Questionselect table Pin
amin.naghdbishi12-Sep-12 4:56
memberamin.naghdbishi12-Sep-12 4:56 
AnswerRe: select table Pin
Kanasz Robert19-Sep-12 4:23
mvpKanasz Robert19-Sep-12 4:23 
QuestionVery useful Pin
pukson11-Sep-12 3:40
memberpukson11-Sep-12 3:40 
AnswerRe: Very useful Pin
Kanasz Robert11-Sep-12 8:20
mvpKanasz Robert11-Sep-12 8:20 
QuestionUsefull article Pin
ipadilla18-Jul-12 0:47
memberipadilla18-Jul-12 0:47 
AnswerRe: Usefull article Pin
Kanasz Robert11-Sep-12 8:21
mvpKanasz Robert11-Sep-12 8:21 
GeneralMy vote of 5 Pin
Mihai MOGA8-Feb-12 6:35
memberMihai MOGA8-Feb-12 6:35 
GeneralRe: My vote of 5 Pin
Kanasz Robert11-Sep-12 8:21
mvpKanasz Robert11-Sep-12 8:21 
GeneralMy vote of 5 Pin
jim lahey31-Jan-12 22:03
memberjim lahey31-Jan-12 22:03 
GeneralRe: My vote of 5 Pin
Kanasz Robert31-Jan-12 23:01
mvpKanasz Robert31-Jan-12 23:01 
GeneralMy vote of 5 Pin
Sridhar Patnayak31-Jan-12 17:51
memberSridhar Patnayak31-Jan-12 17:51 
AnswerRe: My vote of 5 Pin
Kanasz Robert31-Jan-12 22:57
mvpKanasz Robert31-Jan-12 22:57 
QuestionMS SQL Server Agent Pin
ruzenak24-Jan-12 11:01
memberruzenak24-Jan-12 11:01 
AnswerRe: MS SQL Server Agent Pin
Kanasz Robert24-Jan-12 11:03
mvpKanasz Robert24-Jan-12 11:03 
GeneralNice article, IMHO: well worth a 5 Pin
Espen Harlinn22-Jan-12 12:19
mvpEspen Harlinn22-Jan-12 12:19 
GeneralRe: Nice article, IMHO: well worth a 5 Pin
Kanasz Robert24-Jan-12 11:07
mvpKanasz Robert24-Jan-12 11:07 

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 | Terms of Use | Mobile
Web03 | 2.8.150603.1 | Last Updated 22 Jan 2012
Article Copyright 2012 by Kanasz Robert
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid