Solved

Loosing primary key and identity when exporting SQL Server Database.

Posted on 2009-05-06
7
532 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Don’t let your business fall victim to the coming apocalypse – use our Survival Guide for the Fax Apocalypse to identify the risks and signs of zombie fax activities at your business.
I had an issue with InstallShield not being able to use Computer Browser service on Windows Server 2012. Here is the solution I found.
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

932 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now