Using DTS to Copy Database
page 1 of 1
Published: 22 Mar 2005
Unedited - Community Contributed
The ORCS Webteam provides an illustrated step-by-step guide to using DTS (Data Transformation Services) to create a local copy of a production SQL Server database. It is as simple as 1-2-3.
by Web Team at ORCS Web
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 9981/ 8

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.

User Comments

Title: Excellent article   
Name: Kavin Sun
Date: 2008-04-15 4:41:26 AM
it's good for the one who have the very experience in DTS.
Title: Its good   
Name: kanth
Date: 2008-03-20 12:17:00 AM
this is god for one who is starting dts
Title: Copy Database   
Name: Steve Schofield
Date: 2006-09-14 12:56:19 PM
DTS comes with its own programming object model you potentially could add to your project. Here are a few articles that discuss this topic further.

BOL documents all of the DTS API:

A cookbook and examples for SQL
Server 2000 DTS with .NET at
Title: How to use dts in Asp.Net   
Name: Krishna C.
Date: 2006-09-14 9:24:04 AM
This is working properly.But i want know how to use this dts in application).
Title: Using DTS to Migrate Data from one to another   
Name: Steve Schofield
Date: 2006-04-22 3:21:05 PM
I am glad you found this article useful, yes DTS can transfer data to other sources as you mentioned.
Title: Using DTS to Migrate Data from one to another   
Name: Palani R
Date: 2006-04-20 12:36:20 AM

This utility really super,We can transfer all data from one form to another and same kind of option available on Lotus123,Excel,Foxpro but it is Visualised
Title: Copy Database   
Name: Steve Schofield
Date: 2006-04-11 7:49:34 AM
I am glad you found the article useful.
Date: 2006-04-06 5:45:09 AM
i was in critical situtuation to transfer my local procedures to remote server...your suggesstion is now too useful to me ...thanks a lot

Title: backup/restore   
Name: steve schofield
Date: 2005-09-15 10:32:14 PM
Hi J P,

You are correct, this article is not meant to be the end-all solution for every scenerio. YOu are right the backup/restore is probably the safest in a high-volume production environment.

Title: Locks while doing this   
Name: J P
Date: 2005-09-15 1:42:50 PM
What kind of locks this will hold on production database when you run the DTS package? I doubt that it will block everything in a very heavy production environment. I think backup and restore is the safest and easiest solution.
Title: Local database copy for testing   
Name: Steve
Date: 2005-09-15 7:10:02 AM
Hi Janko,

That is a very good point about restoring the database from a backup file. However, this article was intended for those individuals that are in a shared hosting environment that wanted a backup copy of their database without having to contact the hosting provider to request a backup file. The other tools are also good from what I have heard although I do not have any first hand experience with them. Thank you for the feedback.

Steve Schofield
Title: Local database copy for testing   
Name: Janko Lupša
Date: 2005-09-15 7:02:40 AM
Nobody mentioned the obvious thing. Why not take production database backup and restore it locally as test database? You get all the data and schema of production database. Backup can be done while database is being used, there is no downtime.
That is if you want a copy of production database to replace your test version. Other tools like Red-Gate mentioned above are for synchronization of schema (SQL Compare) or data (with Data Compare).
Title: Using DTS To Copy Database Is As Simple As 1-2-3   
Name: Steve Schofield
Date: 2005-03-26 9:49:00 PM
Hi Ed,

Thanks for your comments. I'll take your and Richard's advice and review this product.

Title: This will eat you with FK's too   
Name: Ed
Date: 2005-03-25 11:28:05 AM
This copy method also will _not_ work most times where foriegn keys are present. Since the constraints will fail if a dependent table isn't populated in the correct order.

As Richard stated, RedGate is probably a far better way to go here since it will actually resolve those.

Another possibility is to pass backups or use the "disconnect - copy - reconnect" methodology though this will result in a short period of downtime to production databases.
Title: Using DTS To Copy Database Is As Simple As 1-2-3   
Name: Steve Schofield
Date: 2005-03-25 8:41:17 AM
Hi Richard,

I appreciate your comments, maybe the title of the article is a bit mis-leading. I'm not familiar with those products but appreciate you passing this information along, I'll definitely will review these products. In my experience I've not had any problems using DTS. The original reason I used this process was the identity columns along with primary and foreign keys weren't being re-created on my local database. I've been developing asp/ for years, each time I wanted a copy of my database locally I used this.
Title: This doesn't copy the database   
Name: Richard Schaefer
Date: 2005-03-25 8:15:54 AM
This only copies the tables. What about views, stored procedures, user-defined functions? DTS is notorious for not copying those objects correctly, not respecting dependencies between objects. It simply copies them alphabetically. If View A depends on View B then the copy for View A will fail.

You're better off with a product like Red Gate's SQL Compare and SQL Data Compare.

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-04-14 12:46:07 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search