So, I've noticed that when I import data using Enterprise Manager, it always seems to miss the primary keys and other constraints. Turns out I wasn't using the right option to import my data. I would chose:
Copy table(s) and view(s) from the source database.
However, what I needed to do was:
Copy objects and data between SQL Server databases.
Another thing to watch out for - when given a chance to "Use default options", turn this off and in the Options page, deselect the security options. I'm not sure why this causes problems, but it did with my test. (Kudos to my boss, David An, for this tip!)
Now for a small rant. While Enterprise Manager is a great tool for SQL Server, the interface does some things really stupid. For example - everytime you right click on a remote DB, the tool sends out a ping/request/whatever. However, if you move your mouse while the tool is waiting, you lose the right menu when it finally appears. This is even more of a pain when you are just trying to reconnect and have to wait for the right click menu option, Connect, to show up. Another thing thats a pain in the rear are wizards that don't allow you to look at the rest of the application. So, let's say I'm doing that import from db A to db B. Let's say I forget the name of db B. Once I'm in the wizard, I can't click and scroll down in the rest of Enterprise Manager to get to my db and examine it.
Archived Comments
Kudos to you for posting this! :) I am one of those who have to recreate primary keys, indexes, etc. when copying database objects.
I found this out myself a couple of months ago.. you need to deselect the security check because else the logged in account will be copied to the server your exporting or importing to.. and that account may not exist on that server.
SQL server is great.. but also a pain.. especially with these kind of little nifty things you can''t find in the documentation.. well you can.. after a couple of hours...
The "easiest" way to do this fast is simply to move the mdb ... renaming as required under the new database name you''ve created (as the new database created will automatically create a db and lock). I''ve never found a reliable way to migrate a db with the dts and retain the column cardinality attributes.
Bill, MDB files are for MS Access. I''m talking about SQL Server dbs here. :)