LogoASPAlliance: Articles, reviews, and samples for .NET Developers
Using DTS to Copy Database
by Web Team at ORCS Web
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 13078/ 18

Steve Schofield
March, 3 2005

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.

screen shot 1

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

screen shot 2

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

screen shot 3

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

screen shot 4

  • Click the Next button

screen shot 5

  • 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

screen shot 6

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

screen shot 7

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

screen shot 8

  • 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

screen shot 9

  • Click the OK button
  • This window will appear again

screen shot 10

  • Click the Next button

screen shot 11

  • 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.

Product Spotlight
Product Spotlight 

©Copyright 1998-2021  |  Page Processed at 2021-02-26 1:26:19 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search