12.3. Transferring Data

The SMO TRansfer class is used to programmatically transfer (copy) data and schemas within and between SQL Server instances. This class is in the Microsoft.SqlServer.Management.Smo namespace.

This example shows how to transfer the AdventureWorks database to a new database called AdventureWorksCopy:

    using System;
    using System.Data;

    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;

    class Program
    {
        static void Main(string[] args)
        {
            Server server = new Server("localhost");
            Database db = server.Databases["AdventureWorks"];

            // create the destination database
            Database dbCopy = new Database(server, "AdventureWorksCopy");
            dbCopy.Create(  );

            // transfer the data
            Transfer t = new Transfer(db);
            t.CopyAllTables 
 = true;
            t.Options.WithDependencies 
 = true;
            t.DestinationServer = server.Name;
            t.DestinationDatabase 
 = "AdventureWorksCopy";
            t.CopySchema 
 = true;
            t.CopyData 
 = true;

            // wire up event handler to monitor progress
            t.DataTransferEvent +=
                new DataTransferEventHandler(DataTransferEvent_Handler);

            t.TransferData(  );

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey(  );
        }

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

Partial results are shown in Figure 12-8.

Six properties are set to configure the transfer object in this example:

• The CopyAllTables property is set to TRue.

• The Options.WithDependencies property is set to true.

• The DestinationServer property is set to the name of the destination server for the object copy. In this case, the destination server is the local server, which is the same as the source server.

• The DestinationDatabase property is set to the name of the database on the destination server.

• The CopySchema property is set to true.

• The CopyData property is set to true.

Figure 12-8. Results for transfer data example

There are other optional properties not used in this example that further control the transfer operation.

A DataTransfer event handler is added so that the progress as each database is copied is output to the console window.

Finally, the transferData( ) method is called to perform the data transfer operation.