Transferring Objects and Data Using SQL Transfer Manager

Before using SQL Transfer Manager to transfer data, the destination database must exist, and it must be large enough to contain all objects and data that you will transfer.

To transfer data, you must have SELECT permission in the source database, and you must be the database owner of the destination database. If the source server is a non-Microsoft-based SQL Server, and you have not run the OBJECT42.SQL script on that server, you must also have CREATE PROCEDURE permission in the source database.

If your source server is Microsoft SQL Server version 6.0, the OBJECT60.SQL script must have been run on the server if you are including dependencies. Otherwise, SQL Transfer Manager will try to create the 4.21 dependency-evaluator stored procedure, which will fail due to parser changes in version 6.0.

The following procedure assumes you have started SQL Transfer Manager and have connected to a source and destination server.

    To transfer objects and data using the SQL Transfer Manager window

1. In the From Database box, select the database to transfer data from.

   

2. In the To Database box, select the database to transfer data to.

   The destination database must already exist and have enough space to contain the objects and data you are transferring.

3. Review the path and name of the directory where SQL Transfer Manager will save scripts and other files.

   To change the location where the files will be saved, in the Script directory box, type a new path and/or directory name. If this directory does not already exist, when the transfer occurs, SQL Transfer Manager will create it.

   For information about the scripts and other files saved in this directory, see Files Created by SQL Transfer Manager, later in this chapter.

4. Specify the objects that will be transferred.
   • To transfer all database objects, select the All Objects check box. This is the default selection.
   • To transfer all objects of a type, select the check box for that object type.

     You can select one or more object types: tables, views, triggers, rules, defaults, user-defined datatypes, and/or stored procedures.

   • To transfer specific objects, clear the All Objects check box, select one or more objects from the Available Objects list, and then choose the Add button.
5. Specify whether data will also be transferred.

   To also transfer the data from the source database to the destination database, select the Include Data check box.

6. If you selected Include Data, to create unique clustered indexes after the data transfer (by using the WITH SORTED DATA clause), select the Sorted Data check box.

   If this box is not selected, unique clustered indexes are created prior to data transfer. Nonunique clustered indexes are always created prior to data transfer, whether or not this box is selected. Creating the clustered indexes prior to data transfer is slower during the actual data transfer process, but it eliminates the full table sort that is required if the clustered index is created after the transfer. Nonclustered indexes and triggers are always created after the data is transferred.

   If the destination server's sort order does not match that of the source server, using the SORTED DATA clause is likely to fail. If this happens, there will be an error message in the .LOG file, and after the transfer you must build the indexes yourself without the SORTED DATA clause.

7. To also transfer all objects that depend on the selected object(s) or object type(s), select the Include Dependencies check box.

   If you select this option and the source server is Microsoft SQL Server version 6.0, the OBJECT60.SQL script must have been run on that server. Otherwise, SQL Transfer Manager will try to create the 4.2 dependency-evaluator stored procedure, which will fail due to parser changes in version 6.0. For information about running the OBJECT60.SQL script, see Appendix A, Interoperation with Microsoft SQL Server 4.2.

8. To drop existing objects in the destination database before transferring objects from the source database, select the Include Drops check box.
9. To also transfer segment information to the destination database, select the Include Segments check box.

   Segments must already exist at the destination database. There must be sufficient space on the segment(s) to accommodate the tables you will be transferring. If you are not sure of the segment space requirements, you can check them by selecting the Export Only option, choosing the Transfer button, and then reading the generated .TAB file. For more information about .TAB files, see Files Created by SQL Transfer Manager, later in this chapter. For more information about segments, see Chapter 7, Managing Drives.

10. Specify whether or not security information will be transferred.
    • To add the users from the source database to the destination database, select the All Users check box. Note that if you have a guest user created in the source database, you must also create a guest user in the destination database.
    • To add the groups from the source database to the destination database, select the All Groups check box.
    • To add the login IDs for the users from the source server to the destination server, select the All Logins check box.
    • To include all the user's permissions from the source database, select the Include Permissions check box.
11. Determine the setting of the Export Only option.
    • To generate scripts and also transfer the specified data and objects, clear the Export Only option. This is the default setting.
    • To generate scripts without actually transferring the data or objects, select the Export Only option. For example, to view segment and size usage information about tables without actually performing a transfer, you can use this option and then view the information about the tables in the .TAB file that is created in the script directory.
12. Choose the Transfer button.
    • If the Export Only option is cleared, several files are created in the script directory, and the objects and data you specified are transferred.
    • If the Export Only option is selected, the files are created in the script directory, but no objects or data are actually transferred.

    You can interrupt a transfer once it has begun by pressing CTRL+C.