Transfer Database Task
The Transfer Database task transfers a SQL Server database between two instances of SQL Server. In contrast to the other tasks that only transfer SQL Server objects by copying them, the Transfer Database task can either copy or move a database. The task can copy a database between instances of SQL Server 2000, instances of SQL Server, or one of each. This task can also be used to copy a database within the same server.
The database can be transferred by using online or offline mode. When you use online mode, the database remains attached and it is transferred by using SQL Management Object (SMO) to copy the database objects. When you use offline mode, the database is detached, the database files are copied or moved, and the database is attached at the destination after the transfer finishes successfully. If the database is copied, it is automatically reattached at the source if the copy is successful. In offline mode, the database is copied more quickly, but the database is unavailable to users during the transfer.
Offline mode requires that you specify the network file shares on the source and destination servers that contain the database files. If the folder is shared and can be accessed by the user, you can reference the network share using the syntax \\computername\Program Files\myfolder\. Otherwise, you must use the syntax \\computername\c$\Program Files\myfolder\. To use the latter syntax, the user must have write access to the source and destination network shares.
You can specify whether the task tries to reattach the source database if the database transfer fails.
The Transfer Database task can also be configured to permit overwriting a destination database that has the same name, replacing the destination database.
The source database can also be renamed as part of the transfer process. If you want to transfer a database to a destination instance of SQL Server that already contains a database that has the same name, renaming the source database allows the database to be transferred. However, the database file names must also be different; if database files that have the same names already exist at the destination, the task fails.
When you copy a database, the database cannot be smaller than the size of the model database on the destination server. You can either increase the size of the database to copy, or reduce the size of model.
At run time, the Transfer Database task connects to the source and destination servers by using one or two SMO connection managers. When you create a copy of a database on the same server, only one SMO connection manager is required. The SMO connection managers are configured separately from the Transfer Database task, and then are referenced in the Transfer Database task. The SMO connection managers specify the server and the authentication mode to use when the task accesses the server. For more information, see SMO Connection Manager.
Transferring Databases Between Versions of SQL Server
The Transfer Database task supports SQL Server 2000 and SQL Server. It can transfer a database between instances of SQL Server 2000, instances of SQL Server, and from an instance of SQL Server 2000 to an instance of SQL Server.
Events
The Transfer Database task does not report incremental progress of the error message transfer; it reports only 0% and 100 % completion.
Execution Value
The execution value, defined in the ExecutionValue property of the task, returns the value 1, because in contrast to other transfer tasks, the Transfer Database task can transfer only one database.
By assigning a user-defined variable to the ExecValueVariable property of the Transfer Database task, information about the error message transfer can be made available to other objects in the package. For more information, see Integration Services Variables and Using Variables in Packages.
Log Entries
The Transfer Database task includes the following custom log entries:
SourceSQLServer This log entry lists the name of the source server.
DestSQLServer This log entry lists the name of the destination server.
SourceDB This log entry lists the name of the database that is transferred.
In addition, a log entry for the OnInformation event is written when the destination database is overwritten.
Security and Permissions
To transfer a database using offline mode, the user who runs the package must be a member of the sysadmin server role.
To transfer a database using online mode, the user who runs the package must be a member of the sysadmin server role or the database owner (dbo) of the selected database.
Configuring the Transfer Database Task
You can set properties through SSIS Designer or programmatically.
For more information about the properties that you can set in SSIS Designer, click one of the following topics:
For more information about how to set these properties in SSIS Designer, click the following topic:
Configuring the Transfer Database Task Programmatically
For more information about programmatically setting these properties, click the following topic:
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services