- 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
Here are some programming tips, be it ASP.NET, C#, CSS, Java, JavaScript, PHP, SQL, XHTML, etc. I am director of PHD Computer Consultants Ltd, based in Cumbria, England, UK - we sell our own software and undertake software projects and consultancy.
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:
No comments:
Post a Comment