Solved

Loosing primary key and identity when exporting SQL Server Database.

Posted on 2009-05-06
7
533 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

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

Suggested Solutions

Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

778 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