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 2005, 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.