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:
- Copy data from one of more tables or views
- Write a query to specify the data to transfer
- 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.