ColdFusion Muse

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.

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

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!