THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Import/Export Wizard and indexes, keys etc

Here's one which I've seen much confusion about. Something like "I copy tables from database A to database B but I don't get any indexes, keys, constraints etc. This worked in 2000. Why?".

We need to first understand that that Import/Export Wizard just sit on top if DTS/SSIS. In DTS, you had three choices in the Wizard:

  1. Copy data from one of more tables or views
  2. Write a query to specify the data to transfer
  3. Copy objects and data between SQL Server databases

Now, read above and think of the actual words, they are very descriptive. The thing is that in 2005 (or 2008), option 3 isn't available in the wizard anymore. So what many users end up doing is to use option 1 above. But this was never designed to carry over indexes, keys, triggers etc. This is designed to be generic and work with (more or less) and type of data source (SQL Server, Oracle, DB2 etc).

So the question is how we get the "Copy objects and data between SQL Server databases" functionality in 2005 or 2008? We create an SSIS package in BIDS and select the task type "Transfer SQL Server Objects Task". (For you long-timers: this is the old "Object Transfer" functionality which has existed since the version 4 days, and been exposed in various places in the tools over the years.)

I just now played with this a bit and you probably need to work it a bit to get it do exactly what you want. I didn't get it to work correctly for the Adventureworks database since it thought that the Emplyee table exists in the dbo schema (judgning by error message and Profiler trace). This was even though the GUI clearly show the proper schema. When I selected pubs as source, it worked better (after changing some options).

However, the point of this post is not to troubleshoot possible bugs or quirks in the "Transfer SQL Server Objects Task" task. Now is morning after Swedish midsummer and I'm too tired for that :-) . The point is where to find the functionality that copies not only raw table definition and data - but also indexes, key, triggers etc.

Published Saturday, June 21, 2008 11:14 AM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

David Fernee said:

Cheers mate, you saved me a lot of time!

June 19, 2012 8:59 AM
 

Kandanuru said:

Friend, Your article saved time and could able to handle the task easily

March 6, 2013 10:23 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement