Using DTS To Copy Database
Steve Schofield
March 3, 2005

One of the items I like to do from time to time is have a fresh copy of a production database on a local SQL Server. The local copy usually would be used for performance testing, application development etc. 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/foreign keys and other items. I only remember to do this after the database is copying down. Worse yet working on code for a few hours and can't figure out the problem only to realize the identity column isn't setup.

For an occasional dba's, a picture is worth 1000 words when performing procedures like this. This step-by-step reference helps remind me of the correct way. Hope this helps in your DTS adventures!

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 called OWExample_User with dbo rights on the OWExample database
• Open SQL Enterprise manager
• Locate the local version of the database,
• Right click, All Tasks and Select Import Data

 

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

 

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

 

• Click Next

 

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

 

• Uncheck everything except the tables - "Show all tables"?
• Select All -
• Click "Select All"
• Click Check (This will check everything)

 

• Click OK
• This screen will re-appear -
• Click "OK"

 

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

 

• Click OK
• This window will appear again

 

• Click Next

 

• Click Finish

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