From time to time I like to make a local copy of a production SQL Server database. The local copy may be used for performance testing, application development, and so on. Microsoft SQL Server 2000 uses DTS (Data Transformation Services) to help move data from server to server. One of the things I like to have copied correctly is the identity columns, primary and foreign keys, and other items. Often, I only remember to do this after the database is copying down. Worse yet, I may work on code for a few hours, find that I can't figure out the problem, and finally realize that the identity column is not set up.

For an occasional Database Administrator, a picture is worth a thousand words when performing procedures such as this. This step-by-step reference helps remind me of the correct way. I hope this helps in your DTS adventures!

First, here are a couple of articles of reference from Microsoft.

• HOW TO: Move Databases Between Computers That Are Running SQL Server
• Creating DTS Packages with the DTS Object Model

Step-by-step Procedure.

• Create a local database called OWExample
• Create a local user named OWExample_User with dbo rights on the OWExample database
• Open SQL Enterprise Manager
• Locate the local version of the database, right-click its icon, select All Tasks, and then Import Data

• The DTS Import/Export Wizard will appear
• Type in the DataSource (OWExample.yourserver.com, OWExample_user, P@SSWord)

• Click the Next button
• Type in the local SQL 2000 OWExample_user id and password

• Click the Next button

• Click the Next button
• This window will appear
• Leave the defaults except uncheck the "Copy all objects" and the "Use Default options" checkboxes
• Click the Select Objects button

• Uncheck everything except the "Show all tables" checkbox 
• Click the Select All button
• Click the Check button (this will check everything)

• Click the OK button
• This screen will re-appear
• Click the OK button

• Click the Options button
• Leave "Create…" and "Copy…." and click the Options button
• This window will appear
• Uncheck Copy database users and database roles
• Uncheck object-level permissions

• Click the OK button
• This window will appear again

• Click the Next button

• Click the Finish button

Steve Schofield is a Senior Internet support specialist with ORCS Web, Inc. - a company that provides managed hosting services for clients who develop and deploy their applications on Microsoft Windows platforms.