12.3. Transferring DataThe 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:
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. |