Now that you have learned how to import and export data using the DTS Import/Export Wizard, you are ready to learn how to copy database objects and data between SQL Servers. The ability to copy SQL Server objects and data between SQL databases in different SQL instances is one of the most powerful capabilities of the DTS Import/Export Wizard. The objects you can transfer include tables, views, stored procedures, defaults, rules, constraints, user - defined data types, logins, users, roles, and indexes. When transferring objects between SQL Server instances, some version restrictions apply. You can transfer objects only between multiple instances of SQL Server 2000, from an instance of SQL Server 7 to an instance of SQL Server 2000, and between multiple instances of SQL Server 7. Copying objects is useful for many purposes, including migrating an existing database to a secondary server without taking the database offline, converting a database from one collation sequence to another, and copying logins from a primary server to a standby server.
In the next procedure, you will learn how to copy all the database objects and data from one SQL Server 2000 database into another.
To launch the DTS Import/Export Wizard from within SQL Server Enterprise Manager, right-click the (local) (Windows NT) server icon, point to All Tasks, and then click Export Data.
If you were copying or moving a database between servers, you could also use the Copy Database Wizard for this task.
Click Next to select a data source from which you will copy objects and data. You will select the SBS_OLTP database on your local server (or you can select a remote server if you have one available).
Verify that Microsoft OLE DB Provider For SQL Server appears as the data destination, ensure that Windows Authentication is selected, and ensure that (local) appears in the Server list. In the Database list, select SBS_OLTP, and then click Next to select a data destination. You will create the SBS_OLTP_Copy database as your data destination.
Verify that Microsoft OLE DB Provider For SQL Server appears as the data destination, ensure that Windows Authentication is selected, and ensure that (local) appears in the Server list. In the Database list, select New.
Type SBS_OLTP_Copy in the Name box and then click OK. Click Next to specify what DTS will copy from the data source to the data destination. You will copy objects and data between SQL Server databases.
Click Copy Objects And Data Between SQL Server Databases, and then click Next to specify the objects to copy.
By default, all objects and all data in the source database are copied to the destination database, and if identically named destination objects already exist, they are dropped along with all dependent objects. However, the default settings do not copy extended properties that have been defined on various objects in the database. You must specifically choose to copy extended properties when you copy database objects. If you do not specify the dropping and recreating of existing destination objects, data is appended to the existing data.
If you are copying objects and data between SQL Server 2000 databases with differing collations using the DTS Import/Export Wizard, you must select the Use Collation check box to ensure no data is lost. A collation encodes the rules governing the proper use of characters for either a language, such as Arabic or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages). For more information on collations, see SQL Server Collation Fundamentals in SQL Server Books Online. For more information on using DTS to copy data between databases with differing collations, see Data Conversion and Transformation Considerations in SQL Server Books Online.
Finally, notice the Script File Directory text box. This text box specifies the location in which SQL Server stores the script files used to create the objects in the destination database, the data files used to populate the objects in the destination database, and the log files associated with the object creation and file copy process.
Clear the Copy All Objects check box and then click Select Objects to display the Select Objects dialog box.
In this dialog box, you can view and then select specific tables, views, stored procedures, user-defined functions, defaults, rules, and user- defined data types. This enables you to copy or skip specific objects when copying database objects in the package. In this procedure, you will copy all objects.
Click Cancel and then re-select the Copy All Objects check box.
Clear the Use Default Options check box and then click Options to view the default options in the Advanced Copy Options dialog box. In this procedure, you will use the default settings.
By default, all security settings, indexes, triggers, and primary and foreign keys are copied. However, SQL Server logins are not copied and the scripts are not generated in Unicode.
You should generate scripts in Unicode when the source data contains nchar , nvarchar , or ntext data types or double-byte character set (DBCS) data. For more information on Unicode data, see Unicode Data in SQL Server Books Online.
Quoted identifiers are used when copying objects. This setting encloses all object names in the generated script in quotation marks to delimit an identifier. For more information on quoted identifiers, see System Configuration in SQL Server Books Online.
Click Cancel, re-select the Use Default Options check box, and then click Next to choose whether to execute the package immediately and to save, schedule, or replicate the package.
Click Save DTS Package, click Structured Storage File, and then click Next to provide the details for the save options.
Type SQL_DTS_SBS_1.6 in the Name box, type DTS Step By Step Chapter 1 Package 6 in the Description box, type mypassword in the Owner Password box, and type the following in the File Name box:
C:\Microsoft Press\SQL DTS SBS\Ch1\WorkingFolder\SQL_DTS_SBS_1.6.dts
Click Next to review the selections that you made in the wizard.
Click Finish to save and execute the package.
Click OK to acknowledge that objects were successfully copied from Microsoft SQL Server to Microsoft SQL Server.
You have successfully copied objects and data to a new database. This is one of the simplest methods for copying the data and objects in an existing database to a new database without taking the database offline.