Export (0) Print
Expand All
1 out of 2 rated this helpful - Rate this topic

Transferring Data

The Transfer class is a utility class that provides tools to transfer objects and data.

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.

The Transfer object contains all the functionality of the Transfer objects in DMO and additional SQL Server functionality. However, in SMO, the Transfer object uses DTS to transfer data. Also, the methods and properties that are used to perform data transfers reside on the Transfer object instead of the Database object. Moving functionality from the instance classes to utility classes is consistent with a lighter object model because the code for specific tasks is loaded only when it is required.

The Transfer object does not support data transfers to a target database that has a CompatibilityLevel less than the version of the instance of SQL Server.

Specifically,

  • SQL Server 2005 and SQL Server 2008 do not support data transfer to databases with CompatibilityLevel property of Version80.

  • SQL Server 2005 and SQL Server 2008 do not support data transfer to databases running SQL Server version 7.0.

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see "How to: Create a Visual Basic SMO Project in Visual Studio .NET" or "How to: Create a Visual C# SMO Project in Visual Studio .NET" in SQL Server Books Online.

This code example shows how to transfer schema and data from one database to another using the Transfer object.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database
Dim db As Database
db = srv.Databases("AdventureWorks")
'Create a new database that is to be destination database.
Dim dbCopy As Database
dbCopy = New Database(srv, "AdventureWorksCopy")
dbCopy.Create()
'Define a Transfer object and set the required options and properties.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllTables = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = "AdventureWorksCopy"
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.ScriptTransfer()


This code example shows how to transfer schema and data from one database to another using the Transfer object.

//Connect to the local, default instance of SQL Server.

{ 
   Server srv = default(Server); 
   srv = new Server(); 
   //Reference the AdventureWorks database 
   Database db = default(Database); 
   db = srv.Databases("AdventureWorks"); 
   //Create a new database that is to be destination database. 
   Database dbCopy = default(Database); 
   dbCopy = new Database(srv, "AdventureWorksCopy"); 
   dbCopy.Create(); 
   //Define a Transfer object and set the required options and properties. 
   Transfer xfr = default(Transfer); 
   xfr = new Transfer(db); 
   xfr.CopyAllTables = true; 
   xfr.Options.WithDependencies = true; 
   xfr.Options.ContinueScriptingOnError = true; 
   xfr.DestinationDatabase = "AdventureWorksCopy"; 
   xfr.DestinationServer = srv.Name; 
   xfr.DestinationLoginSecure = true; 
   xfr.CopySchema = true; 
   //Script the transfer. Alternatively perform immediate data transfer 
   // with TransferData method. 
   xfr.ScriptTransfer(); 
} 
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.