Saturday, February 6, 2010

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
Note: 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:
Step one: With the Enterprise Manager open, right-click the source database, select All Tasks, and click Generate SQL Script.
Image of step one
Step two: 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.
step 2 for 3846
Step three: From the Formatting tab, click to select Generate the CREATE command for each object. Image of step three Step four: From the Options tab, click to clear all Security Scripting Options. Click OK. Image of step four Step five: 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. Warning: If you chose the option Generate the DROP 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. Warning: 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. Step six: Execute the script on the destination server.
Transferring Databases Using the DTS Import/Export Wizard:
Note: 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.
Step one: With the Enterprise Manager open, right-click the source database, select All Tasks, and click Export Data. Image of step one Step two: From the Data Transformation Services Import/Export Wizard dialog box, click Next. Image of step two Step three: In the Choose a Data Source dialog box, from the Data Source drop-down menu, click to select your data source type. Image of step three and four Step four: From the Server drop-down menu, click to select the source server. Step five: Click to choose the preferred method of authentication. Note: If you choose Use SQL Server Authentication, you will need to provide a Username and Password. Image of step five, six, and note Step six: From the Database drop-down menu, click to select the database you want to copy, and click Next. Step seven: In the Choose a destination dialog box, from the Destination drop-down menu, click to select your destination data source type. Image of step seven and eight Step eight: From the Server drop-down menu, click to select the destination server. Step nine: Click to choose the preferred method of authentication. Note: If you choose Use SQL Server Authentication, you will need to provide a Username and Password. Image of step nine, ten, and note Step ten: From the Database drop-down menu, click to select the database you will be copying to, and click Next. Step eleven: 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. Image of step eleven Step twelve: From the Select Source Tables and Views dialog box, click to select all available tables, and then click to clear all views. Click Next. Image of step twelve Step thirteen: From the Save, schedule, and replicate package dialog box, click to select Run Immediately. Click Next. Image of step thirteen Step fourteen: From the Completing the DTS Import/Export Wizard dialog box, review the Summary and click Finish. Image of step fourteen Step fifteen: From the DTS Import/Export Wizard confirmation dialog box, click OK. Image of step fifteen  From the Executing Package dialog box, wait for the process to complete and click Done. Image of step sixteen Note: 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. Image of step eighteen  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. Image of step nineteen and twenty  Click Next.  From the Select Objects dialog box, click to clear Show all tables and Show user-defined data types." Image of step twenty-one and twenty-two  Click OK.  In the Advanced Copy Options dialog box, from the Security options section, click to clear all three security options. Click OK. Image of step twenty-three  From the Save, schedule, and replicate package dialog box, click to select Run immediately. Click Next. Image of step twenty-four  From the DTS Import/Export Wizard confirmation dialog box, click OK. Image of step twenty-five  From the Executing Package dialog box, wait for the process to complete and click Done. Image of step twenty-six
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)