Skip to content

transferring-data.md

Latest commit

 

History

History
119 lines (104 loc) · 5.71 KB

transferring-data.md

File metadata and controls

119 lines (104 loc) · 5.71 KB
title Transferring Data | Microsoft Docs
ms.custom
ms.date 10/20/2016
ms.prod sql-server-2014
ms.reviewer
ms.technology
ms.topic reference
topic_type
apiref
helpviewer_keywords
data transfers [SMO]
transferring data
ms.assetid eea255c3-8251-40f0-973b-fe4ef6cb5261
author stevestein
ms.author sstein
manager craigg

Transferring Data

The xref:Microsoft.SqlServer.Management.Smo.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. xref:Microsoft.SqlServer.Management.Smo.Table data is transferred with a dynamically created DTS package.

The xref:Microsoft.SqlServer.Management.Smo.Transfer object contains all the functionality of the xref:Microsoft.SqlServer.Management.Smo.Transfer objects in DMO and additional [!INCLUDEssNoVersion] functionality. However, in SMO in [!INCLUDEssSQL11], the xref:Microsoft.SqlServer.Management.Smo.Transfer object uses the SQLBulkCopy API to transfer data. Also, the methods and properties that are used to perform data transfers reside on the xref:Microsoft.SqlServer.Management.Smo.Transfer object instead of the xref:Microsoft.SqlServer.Management.Smo.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 xref:Microsoft.SqlServer.Management.Smo.Transfer object does not support data transfers to a target database that has a xref:Microsoft.SqlServer.Management.Smo.Database.CompatibilityLevel%2A less than the version of the instance of [!INCLUDEssNoVersion].

Example

[!INCLUDEssChooseProgEnv]

Transferring Schema and Data from One Database to Another in Visual Basic

This code example shows how to transfer schema and data from one database to another using the xref:Microsoft.SqlServer.Management.Smo.Transfer object.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 2008R2 database
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'Create a new database that is to be destination database.
Dim dbCopy As Database
dbCopy = New Database(srv, "AdventureWorks2012Copy")
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 = "AdventureWorks2012Copy"
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.ScriptTransfer()

Transferring Schema and Data from One Database to Another in Visual C#

This code example shows how to transfer schema and data from one database to another using the xref:Microsoft.SqlServer.Management.Smo.Transfer object.

{  
            Server srv;  
            srv = new Server();  
            //Reference the AdventureWorks2012 database   
            Database db;  
            db = srv.Databases["AdventureWorks2012"];  
            //Create a new database that is to be destination database.   
            Database dbCopy;  
            dbCopy = new Database(srv, "AdventureWorks2012Copy");  
            dbCopy.Create();  
            //Define a Transfer object and set the required options and properties.   
            Transfer xfr;  
            xfr = new Transfer(db);  
            xfr.CopyAllTables = true;  
            xfr.Options.WithDependencies = true;  
            xfr.Options.ContinueScriptingOnError = true;  
            xfr.DestinationDatabase = "AdventureWorks2012Copy";  
            xfr.DestinationServer = srv.Name;  
            xfr.DestinationLoginSecure = true;  
            xfr.CopySchema = true;  
            //Script the transfer. Alternatively perform immediate data transfer   
            // with TransferData method.   
            xfr.ScriptTransfer();  
        }

Transferring Schema and Data from One Database to Another in PowerShell

This code example shows how to transfer schema and data from one database to another using the xref:Microsoft.SqlServer.Management.Smo.Transfer object.

#Connect to the local, default instance of SQL Server.  
  
#Get a server object which corresponds to the default instance  
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server  
  
#Reference the AdventureWorks2012 database.  
$db = $srv.Databases["AdventureWorks2012"]  
  
#Create a database to hold the copy of AdventureWorks  
$dbCopy = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Database -ArgumentList $srv, "AdventureWorksCopy"  
$dbCopy.Create()  
  
#Define a Transfer object and set the required options and properties.  
$xfr = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer -ArgumentList $db  
  
#Set this objects properties  
$xfr.CopyAllTables = $true  
$xfr.Options.WithDependencies = $true  
$xfr.Options.ContinueScriptingOnError = $true  
$xfr.DestinationDatabase = "AdventureWorksCopy"  
$xfr.DestinationServer = $srv.Name  
$xfr.DestinationLoginSecure = $true  
$xfr.CopySchema = $true  
"Scripting Data Transfer"  
#Script the transfer. Alternatively perform immediate data transfer with TransferData method.  
$xfr.ScriptTransfer()