If you try to transfer a database from one SQL Server to another using the Enterprise Manager data transfer wizard, you may notice that your primary key and identity properties do not transfer correctly.

To transfer these, you have to use the “Transfer Objects” and change a few of the defaults. If you just accept all the defaults, you won’t get any of your keys on the copied database.

1. Right click on the source database and from All Tasks click “Export”

2. Choose the source DB and click next

3. Choose the destination DB and click next

NEXT STEPS ARE IMPORTANT SO KEYS AND IDENTITIES IMPORT CORRECTLY - PAY ATTENTION!!

4. Choose the option to “Copy Objects and Data between SQL Server Databases”

5. Check the box that says “Include Extended Properties” (I haven’t confirmed, but I think this is what copies over the Identity property)

6. If you don’t want all the tables, de-select “Copy all Objects” and click the “Select Objects…” button to choose the tables to copy.

7. De-select “Use Default Options” and click the “Options…” button. On the options screen, de-select “Copy database users and database roles” unless you have admin access to both servers and you want the same usernames to be used on both servers. If you get errors that the user does not exist, or you get duplicate tables with different username (schemas) then this is the cause.

8. Click next and run immediately or schedule. The data migration wizard should run. Check your new tables to make sure they have keys and identities correct.

Another thing to pay attention is that you can have duplicate tables like so:

[dbo].[Table1]
[username].[Table1]

This can cause all kinds of grief because they are treated as totally different tables and you will see only the one for which you are logged in. So, if you login to to SQL server as sa, you’ll see both tables. Your web app might only see the user-specific one. Pay attention to step 7 above to prevent this situation.

Share This