Loosing primary key and identity when exporting SQL Server Database.

Posted on 2009-05-06
Medium Priority
Last Modified: 2013-12-24

Its a common task to move sqlserver express databases from one server to another and we always have the same problem:

After the export process we have to open the destination db and set the primary key and identity on all tables as they loose this attribute when they are exported.

Is there a workaround for this? as when we export we select the option "enable identity insert and drop and recreate tables" for all tables.


Question by:Ederwainer
  • 2
  • 2
  • 2
  • +1
LVL 31

Expert Comment

ID: 24315850
I guess only IDENTITY key loss not PK.
LVL 12

Expert Comment

ID: 24315864
why not try backing the databases up and restoring on the new server - that will keep all PK's and Identity fields.
LVL 31

Expert Comment

ID: 24315887
well backup and restore would be great option but for complete database not for few tables. I guess in SSIS package you can keep your PK intact as it is
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

LVL 14

Expert Comment

ID: 24316515
Try using the "Microsoft SQL Server Database Publishing Wizard" to move your database schema and data to the target SQL Server rather than using an export, which will not transfer database settings like primary keys, etc.

Microsoft SQL Server Database Publishing Wizard 1.1

LVL 14

Accepted Solution

wolfman007 earned 1000 total points
ID: 24316583
The following website has a tutorial for deploying using the Microsoft SQL Server Database Publishing Wizard, it covers how to use it to easily generate a .SQL installation file of a local SQL Express database that you can then copy to a remote hosting account and use to re-create a SQL Server database.

Recipe: Deploying a SQL Database to a Remote Hosting Environment (Part 1)

Author Comment

ID: 24316820
I cannot use backup and restore, as we are coming from shared server, which will not give me that kind of permission to retrieve the backup files

I will try wolfmann007 approach and post how it goes.

Thanks for the prompt responses!


Author Closing Comment

ID: 31578497
I appreciate all the help and as soon as I install Visual BAsic 2005, I will run the suggested procedure.

Thanks wolfmann.

Eder Wainer

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

WARNING:   If you follow the instructions here, you will wipe out your VTP and VLAN configurations.  Make sure you have backed up your switch!!! I recently had some issues with a few low-end Cisco routers (RV325) and I opened a case with Cisco TA…
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question