Database Exporting Using DTS
A tedious but necessary task is moving a database. Whether it's installing a new application or migrating servers, just about all developers have had to do it. If you've ever moved DATA from 1 SQL database to another you know there are many ways make it work. You can restore from backup; detach, copy and re-attach a file; or use DTS (Data Transformation Services).
I often find myself using DTS because I do not have physical access to the DB Server, or perhaps because the new database already contains some objects or tables that I want or need. For example, if I have an application that manages inventory and an application that manages permissions and I wanted to move them into a single database for ease of management - DTS would be the way to go. Actually DTS has some powerful capabilities that most of us leave largely untouched. Most of the time we are interested in a one time migration of data from database "A" to database "B". DTS does good job of this. It's fast and handles the migration very smoothly. There are some issues however.
The first issue is the Identity Field. This field is an integer that automatically increments and it is often used as a primary key. If you DTS tables using the "create" option (where the table is created and then the data is moved), the identify flag is not set. The first time after the migration that you try to insert into the table you will get an error that says something like column does not allow nulls - insert failed. That's because the trigger necessary to populate the column is not set. You will have to set that flag programmatically (or using enterprise manager). The same is true for all defaults.There are also problems with Stored procedures, triggers, defaults keys and indexes. None of these items are brought over by the DTS engine so they will all need to be created separately. If the database you are moving is complex this can be a real headache. There is a way to use DTS effectively that will avoid these problems but it involves another one of my favorite MS SQL tools - the "Script generator". Here are the steps:
Step 1 - Script generator
Open enterprise manager and navigate to the database you plan to export. "right-click" on any table and choose All Tasks-->"generate SQL scripts". In the dialog box that appears choose "show all" then check the boxes for "tables, views, stored procedures and defaults". You can also choose user defined data types, UDF's and rules. Next Click on the "formatting" tab and select "generate scripts for all dependent objects". Leave the "create" and "drop" options checked. Finally, click on the "options" tab and select indexes, foreign keys and primary keys, triggers and "full-text" indexes (if you have any). Click ok and the file save dialog will appear. choose a name for the file and click save. The generate will generate create and alter statements for everything in your database. Open the file in query analyzer.
Step 2 - check the user
The script generator generates table definitions that specify the table owner and creator. For example, if user "bob" creates a table the table create will indicate CREATE TABLE [bob].[location]. If you want the owner to be dbo (aliased to any owner of the database) you should change it to CREATE TABLE [dbo].[location]. This can usually be done with a careful "replace" operation from the edit menu of SQL query analyzer. If you don't do this you will likely need to include the table owner name in your queries.
Step 3 - Divide up Script
Your next step is to divide your script into 2 parts. Part 1 is the "create table" part and part 2 is the "add indexes, keys and objects" part. This is pretty easy because the script that has been generated starts with all the drops, then proceeds to create all the tables, so all you have to do is scroll down till you see the last "create table" statement and the first "anything else".
CREATE TABLE [dbo].[location] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[area] [nvarchar] (2) COLLATE SQL_Latin ... NOT NULL ,
[urlbooking] [nvarchar] (100) COLLATE SQL_Latin ... NULL ,
[online] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
... this is the start of file 2 - everything except
the drops and table creates....
ALTER TABLE [dbo].[location] WITH NOCHECK ADD
CONSTRAINT [PK_hotels] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
Step 4 run the first script
Run the "drops" and "table creates" script. This will create the tables on your DB. Why not run both scripts? Well, if you have any foreign keys defined you will generate constrain errors. The reason is logical when you think about it. DTS copies one table at a time. It is very likely going to copy a table with a foreign key in it before it copies a table with the primary key in it (thus the constraint violation). But the constraints are defined in the second file so they don't exist yet - the DTS has a much better chance of succeeding.
Step 5 DTS Routine
Now you can run DTS import/export safely without having to adjust the transformation settings. Simply selecting the tables you desire and using the default settings for transformation will work - and it will even keep your identity flag intact!!
Step 6 - Run the second Script
Now that the data is all there you are ready to run Script number 2. This script will set your defaults, keys, triggers and stored procedures. There is one "Gotcha" to watch out for here. If you have stored procedures that reference other stored procedures, defaults or indexes that do not yet exist in the database the "create procedure" routine will generate a warning message. The reason is because the execution compiler tries to verify that dependent objects exist when it creates the stored procedure. Usually the SP is referencing an object that is being created later in the script - so you can usually safely ignore these errors.
That's it, your done. You should have all the objects you need along with the tables at this point. Happy querying!
4 Comments
-
I didn't read the whole post yet, but I move stored procs, triggers, defaults, etc around all the time with DTS. I just use the "copy SQL server objects from one location to another" option. I usually turn on "extended properties", and turn off the copying of users and permissions.
Am I missing something? -
Dave - I do the same thing, and now feel reinforced in my practice of doing so :)
After a recent experience with a big collection of DTS packages, I'd also encourage folks to use the Dynamic Properties task and workflow to separate the configuration of DTS packages from the packages themselves via things like INI files. -
Ouch... I never tried that option - for lack of careful reading I suppose. I had assumed it was for copying just the schema without the data - but now I see you can copy the data as well. Hmmm... thanks, I'll have to try it :) I still love the script generater though (ha). -
I've centered 1/2 of my backup/redundancy system around DTS and the 'last of the radio buttons' (which I don't remember what the heck it says, but it has already been mentioned).
I schedule nightly copies of all 9 of my databases onto my redundant server- all the data, stored procedures, tables, etc gets moved every night. This goes to an off-site server which is a duplicate that can be used in case of catostrophic problems with my main server. I feel pretty good about this set-up...worst case scenario, a days worth of work is gone.
But I also do a backup, and save the files to multiple places. This is the second part of my plan...and one I have never had to resort to. My redundant server has done a great job the two or three times I have had to bring it up. (Power outage, server upgrade, and dead drive in main server)
Sites to Watch
Aggregators
RSS
