- Ensure that SQL Server Management Studio and Import and Export Data tools are installed.
- Ensure that the SQL Server (SQLEXPRESS) and SQL Server Browser services are started.
- You probably need to make sure that your login has admin access to SQL Server Express.
- 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
- Open Management Studio and connect to (local)\SQLEXPRESS
- Click right on Databases then select Attach, then click Add and choose your MDF
- Amend "Attach as" if desired and check the MDF and LDF file paths below, then click OK
- Do the same for the other database
- Open the Import and Export Wizard
- Select the SQL Server Native Client
- Enter (local)\SQLEXPRESS in the Server name box
- Choose the required source database below, then click Next
- Do the same to choose the destination database
- Select "Copy data from one or more tables and views"
- Select which tables you wish to copy
- For each table, click Edit Mappings.
- Ensure that "Enable identity insert" is ticked
- Choose the appropriate action on the left, eg "Delete rows in destination table", then click OK
- Remember: do this for each table
- Click Next, then Next then Finish
- Open up the destination database in Management Studio to confirm that the tables have got the correct data.
- 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
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: