04 June 2016

Microsoft SQL Server Express copying tables using Identity Insert

I recently needed to copy some tables from one SQL Server Express database to another - as part of a server move on a CMS where I wanted a fresh install to clean things up. I needed to copy several tables across, keeping the primary keys the same so as to ensure the data integrity. Here's what I had to do to do the transfer:
  1. Ensure that SQL Server Management Studio and Import and Export Data tools are installed.
  2. Ensure that the SQL Server (SQLEXPRESS) and SQL Server Browser services are started.
  3. You probably need to make sure that your login has admin access to SQL Server Express.
  4. I started by copying the MDF and LDF files (for both databases) to a new location so I can work with a clean copy that won't be used by something else
  5. Open Management Studio and connect to (local)\SQLEXPRESS
  6. Click right on Databases then select Attach, then click Add and choose your MDF
  7. Amend "Attach as" if desired and check the MDF and LDF file paths below, then click OK
  8. Do the same for the other database
  9. Open the Import and Export Wizard
  10. Select the SQL Server Native Client
  11. Enter (local)\SQLEXPRESS in the Server name box
  12. Choose the required source database below, then click Next
  13. Do the same to choose the destination database
  14. Select "Copy data from one or more tables and views"
  15. Select which tables you wish to copy
  16. For each table, click Edit Mappings.
  17. Ensure that "Enable identity insert" is ticked
  18. Choose the appropriate action on the left, eg "Delete rows in destination table", then click OK
  19. Remember: do this for each table
  20. Click Next, then Next then Finish
  21. Open up the destination database in Management Studio to confirm that the tables have got the correct data.
  22. You had probably best use Tasks+Detach in Management Studio to make sure that all connections are dropped before copy the destination database files into the desired location
Not so bad after all. To celebrate, here's a photo of some nicely padded swallow eggs:

No comments: