Summary: DTS Tutorial
Keywords: 3846, DTS, convert, transfer, data transformation
Description: a description goes here
Using Data Transformation Services (DTS)
This procedure details the recommended method for transferring a database from a development server to one of our shared servers. The first half of the article covers how to generate an SQL script for all database objects. The second half of the article covers how to transfer the database to the destination server using the DTS Import/Export Wizard.
Unfortunately, the transfer functionality is limited (due to the non-DBO privileges of the user). Using the DTS Import/Export Wizard, it is not possible to transfer user-defined data types. Also, indexes, keys, and constraints may not transfer correctly. It might be possible to use a custom DTS script to accomplish a full transfer of all objects, however, that would be beyond the scope of our support.
This procedure is applicable to DTS transfers using the following SQL Server combinations:
- SQL 7 to SQL 7
- SQL 7 to SQL 2000
- SQL 2000 to SQL 2000
In this procedure, SQL 2000 is used as an example. If you are using SQL 7, the images might appear slightly different, but the steps will remain the same. Generating SQL Scripts Using Enterprise Manager:
With the Enterprise Manager open, right-click the source database, select All Tasks, and click Generate SQL Script.
Within the Generate SQL Scripts dialog box, from the General tab, first click the Show All button and then click to select Script all objects.
From the Formatting tab, click to select Generate the CREATE <object> command for each object.
From the Options tab, click to clear all Security Scripting Options. Click OK.
Once complete, open the script using a text editor, and replace all instances of dbo (or whichever userID owned the objects on the source server) with your userID. Also, remove any calls to the setuser function, as these will fail.
If you chose the option Generate the DROP <object> command for each object in Step 3, you will need to leave the reference to dbo in the parts of the script where system tables such as sysobjects, systypes, and so on. are being qualified.
Enterprise Manager does not always generate the statements in the correct order, so it may be necessary to move things around in the script. For example, it might generate a statement for a stored-procedure or a user-defined function that references a table that was created further down in the script.
Execute the script on the destination server.
Transferring Databases Using the DTS Import/Export Wizard:
If your tables have foreign key constraints and relationships defined, you might have to transfer your data in separate batches so that you dont violate referential integrity.
With the Enterprise Manager open, right-click the source database, select All Tasks, and click Export Data.
From the Data Transformation Services Import/Export Wizard dialog box, click Next.
In the Choose a Data Source dialog box, from the Data Source drop-down menu, click to select your data source type.
From the Server drop-down menu, click to select the source server.
Click to choose the preferred method of authentication.
If you choose Use SQL Server Authentication, you will need to provide a Username and Password.
From the Database drop-down menu, click to select the database you want to copy, and click Next.
In the Choose a destination dialog box, from the Destination drop-down menu, click to select your destination data source type.
From the Server drop-down menu, click to select the destination server.
Click to choose the preferred method of authentication.
If you choose Use SQL Server Authentication, you will need to provide a Username and Password.
From the Database drop-down menu, click to select the database you will be copying to, and click Next.
From the Specify Table Copy or Query dialog box, click to choose whether you want to copy one or more tables or the results of a query from the source database. Click Next.
From the Select Source Tables and Views dialog box, click to select all available tables, and then click to clear all views. Click Next.
From the Save, schedule, and replicate package dialog box, click to select Run Immediately. Click Next.
From the Completing the DTS Import/Export Wizard dialog box, review the Summary and click Finish.
From the DTS Import/Export Wizard confirmation dialog box, click OK.
From the Executing Package dialog box, wait for the process to complete and click Done.
All data and tables should be copied. Now it is time to copy over the other SQL Server objects.
Repeat Step 1 through 10 of the DTS Import\Export Wizard section.
From the Specify Table Copy or Query dialog box, click to select Copy objects and data between SQL Server databases. Click Next.
From the Select Objects to Copy dialog box, click to select Create destination objects (tables, views, stored procedures, constraints, etc.) and Drop destination objects first.
Click Next.
From the Select Objects dialog box, click to clear Show all tables and Show user-defined data types."
Click OK.
In the Advanced Copy Options dialog box, from the Security options section, click to clear all three security options. Click OK.
From the Save, schedule, and replicate package dialog box, click to select Run immediately. Click Next.
From the DTS Import/Export Wizard confirmation dialog box, click OK.
From the Executing Package dialog box, wait for the process to complete and click Done.
