4.29.2012

Copy MS Sql Database And Maintaining Primary Keys

Seems there is a trick to copying a MSSQL database and maintaining the keys.   You would think something called the Import/Export Wizard would make it easy but it hides some things.

First,  I'm not sure I had to do this but I dropped all tables from the DESTINATION DB using and undocumented stored procedure:
exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' "

Next, you want to script the tables from the TARGET DB.   You might think you would go to he SCRIPT option but that is for the entire db and I just want the tables.

Script just the tables from the TARGET DB using MS SQL SERVER MANAGEMENT TOOLS.  Right click on the DB and selecting  TASKS  >  GENERATE SCRIPTS menu option.    The wizard is pretty straight forward.

Run the generated script against your DESTINATION DB.  Make sure you change the USE statement at the top of the script.

Now run the Import/Export Wizard.  BUT WAIT, THERE'S ANOTHER CATCH !   On the screen where you select the tables to import,  look at the bottom for a button labeled "EDIT MAPPINGS".   On this screen, make sure you select "Enable identity insert".

Job Done.