Solved

Loosing primary key and identity when exporting SQL Server Database.

Posted on 2009-05-06
7
534 Views
Last Modified: 2013-12-24
Hello,

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.

Thanks!

Eder
0
Comment
Question by:Ederwainer
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 31

Expert Comment

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

Expert Comment

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

Expert Comment

by:RiteshShah
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
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 14

Expert Comment

by:wolfman007
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

http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
0
 
LVL 14

Accepted Solution

by:
wolfman007 earned 250 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)
http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx
0
 

Author Comment

by:Ederwainer
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!

Eder
0
 

Author Closing Comment

by:Ederwainer
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
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data center, now-a-days, is referred as the home of all the advanced technologies. In-fact, most of the businesses are now establishing their entire organizational structure around the IT capabilities.
I had an issue with InstallShield not being able to use Computer Browser service on Windows Server 2012. Here is the solution I found.
Viewers will learn how to connect to a wireless network using the network security key. They will also learn how to access the IP address and DNS server for connections that must be done manually. After setting up a router, find the network security…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

839 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