Transfer Management Interface

SQL Enterprise Manager includes a transfer management interface that you can use to transfer some or all objects from one database to another. The SQL Transfer Manager application shipped with earlier releases is no longer provided.

The transfer management interface eases migration of databases between servers. With the transfer management interface, you can:

Copy all types of objects, or only some types.
Copy all objects of a specific type, or only some objects of a specific type.
Transfer schema and data, only schema, or only data.
Append or replace existing data.
Drop destination objects before copying schema.
Include the objects that depend on a transferred object.
Use default scripting options, or customize the scripts that are used to copy objects and security information.
Execute a transfer immediately, or schedule the transfer to execute one time or on a recurring basis.

The destination server must be a Microsoft SQL Server version 6.5 server. The source server can be a Microsoft SQL Server version 4.x or 6.x server, or a SYBASE® server.

To transfer objects between databases

From SQL Enterprise Manager, in the Server Manager window select a server, expand its tree, open its Databases folder, and select a database.
From the Tools menu, choose Database/Object Transfer.
Complete the Database/Object Transfer dialog box that appears.



Dialog box option	Description
Source Server	Specifies the server you are currently administering by default. To select another server, expand the box.
New Source	Opens the Register Server dialog box, which is used to register a server that is not listed in the Source Server box.
Foreign Source	Opens the Foreign Source dialog box, which is used for non-registered servers. For example, a SYBASE server is not registered.
Destination Server	Specifies a server to transfer data to. Note that the source server and the destination server can be the same server. If the required server name is not included in the list, add it to the list. Choose the New Server button and complete the Register Server dialog box that appears.
New Destination	Adds a server name to the Destination Server list. Choose this button and complete the Register Server dialog box that appears.
Destination Database	Specifies the database to transfer data to. Note that the database must already exist and have enough space for the objects and data that you are transferring. The destination database must be different from the source database.
Copy Schema	Copies the table schema from the source database to the destination database.
Drop Destination Objects First	Drops objects from the destination database before objects are transferred from the source database.
Include Dependency Objects	Includes all objects that depend on the object(s) or object type(s) selected for transfer.
Copy Data	Copies table data from the source database to the destination database.
Replace Existing Data	Replaces existing data in the destination tables with transferred data. To retain existing data and append transferred data, clear this option.
Transfer All Objects	Transfers all objects in the database, including tables, views, stored procedures, defaults, rules, and user-defined datatypes.
Choose Objects	Transfers only selected objects, including tables, views, stored procedures, defaults, rules, and user-defined datatypes. You can transfer all, some, or no objects of a particular type. Choose this button and complete the Choose Objects to be Transferred dialog box that appears.
Use Default Scripting Options	Specifies that default object and security scripting options will be used. The options for the default object automatically create scripts that re-create in the destination database table triggers, table DRI, table bindings, nonclustered and clustered indexes, and owner-qualified objects from the source database. The options for default security re-create users and groups, logins, and object and statement permissions.
Scripting Options	Specifies which scripting options to use. This is an advanced option. Choose this option and complete the Transfer Scripting Options dialog box that appears. The options for the default object can automatically create scripts that re-create in the destination database table triggers, table DRI, table bindings, nonclustered and clustered indexes, and owner-qualified objects from the source database. The options for default security can re-create users and groups, logins, and object and statement permissions.
Save Transfer Files in Directory	Specifies where transfer scripts are stored. The default is C:\MSSQL\BINN. To change the location, type a new path in this box. If the directory does not already exist, SQL Enterprise Manager will create it.
Start Transfer	Executes the transfer immediately, as defined by the selections made in this dialog box.
Schedule	Schedules the transfer to occur immediately, one time, or on a recurring basis. Scheduling an immediate transfer (instead of choosing Start Transfer) provides the advantage of executing the transfer as a scheduled (background) task instead of interactively.
View Logs	Displays the transfer log after the transfer has completed.

To execute the transfer immediately, choose Start Transfer. To schedule the transfer, choose Schedule.

For more information about transferring objects, see the online Help for SQL Enterprise Manager.