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:
- 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
Not so bad after all. To celebrate, here's a photo of some nicely padded swallow eggs:
No comments:
Post a Comment