Transferring Data and Database Objects

In addition to performing import, export, and data transformations between heterogeneous data sources, you can use the DTS Import and DTS Export wizards to transfer objects and data from one Microsoft® SQL Server™ version 7.0 database to another. The objects you can transfer include tables, views, stored procedures, defaults, rules, user-defined data types, logins, users, roles, and indexes.

After you specify the source and destination SQL Server databases to use, and have chosen the option to transfer object and data, select which of the objects to transfer.

Use this To do this
Create destination objects Create destination objects for all objects (tables, view, stored procedures, referential integrity constraints, or indexes) to be transferred (default).
Drop destination objects first Drop all corresponding destination objects before creating new ones as a result of task execution.
Include all dependent objects Include all dependent objects, such as the tables supporting a particular view, in the transfer of data.
Copy data Enable the copying of SQL Server data from source to destination, and enable the copy options specified below (default).
Replace existing data Overwrite any existing data in the destination objects with new data from the specified source.
Append data Retain existing destination data, and append new data from the specified source.
Transfer all objects Transfer all objects associated with the specified data source (default). Clearing this box allows you to select which objects you want to transfer. Transferring selected objects is useful if you want to reduce overhead, or are only working with a narrow subset of objects.
Select objects Select which objects are available for transfer from a list, if the Transfer all objects box is cleared.
Use default options Sets the advanced transfer options to their defaults (default). (To view the advanced transfer options, clear this box and click Options.)
Options Access the advanced transfer options, if the Use default options box is cleared.
Script file directory Specify that the SQL statements required to perform the transfer operation are stored in an SQL script in the specified directory.

If you choose Select objects rather than Transfer all objects, these choices are displayed in Select Object dialog box.

Use this To do this
Show all tables Display all tables in the database.
Show all views Display all views in the database.
Show all stored procedures Display all stored procedures in the database.
Show all defaults Display all defaults in the database.
Show all rules Display all rules in the database.
Show user-defined data types Display all user-defined data types in the database.
Select All Select all objects in the database. You can select an individual object by clicking the object name. You can select multiple objects by pressing CTRL while clicking object names. You can select a range of objects by pressing SHIFT while clicking the first and last items in the range.
Check Place a check next to all selected objects.
Uncheck Remove checks from all selected objects.

If you choose Options rather than Use default options, these choices are displayed in Select Advanced Transfer Options dialog box.

Use this To do this
Transfer database users and database roles Transfer all database users and roles.
Transfer SQL Server logins Transfer all SQL Server logins.
Transfer object-level permissions Transfer all object-level permissions.
Transfer indexes Transfer indexes for all tables transferred.
Transfer full text searches Transfer full text searches for all tables transferred.
Transfer PRIMARY and FOREIGN keys Transfer PRIMARY and FOREIGN key definitions for all tables transferred.
Use quoted identifiers when transferring objects Enclose all object names in quotes.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.