?
Solved

Loosing primary key and identity when exporting SQL Server Database.

Posted on 2009-05-06
7
Medium Priority
?
539 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 
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 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)
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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

PRTG Network Monitor lets you monitor your bandwidth usage, so you know who is using up your bandwidth, and what they're using it for.
This program is used to assist in finding and resolving common problems with wireless connections.
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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

762 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