Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
SQL Server 2000 Export Capabilities for SQL Server 2005

SQL Server 2005 brings a lot to the table (how about those cube models, eh?!?), but it falls far short of SQL Server 2000 when it comes to export/import capabilities.  Assume you have a SQL Server 2005 database on a remote server which you want to make a perfect copy of the data locally.  If you don't have access to the remote server's file server to retrieve a backup of the source database, you're out of luck for copying the data locally.  The export/import capabilities either 1) allow you to copy the database perfectly but then abandon the identity/primary key and foreign key settings (i.e., drops them altogether), or 2) allow you to copy the data, respecting all the keys, but then resetting your identity values.  So if the source DB has a table with a sequence of identity keys with a gap - 1, 2, 3, 4, 6, 7 - it'll be copied over with no gap, resetting the identity values - 1, 2, 3, 4, 5, 6.  Needless to say, this corrupts your data and makes the copy essentially useless.

I've tried this countless times with every possible export/import configuration to no avail.  Countless complaints elsewhere echo the same problem.  (Dave Donaldson, in the comments, makes the valid point that if you use the export/import tool with the "Enable Identity Insert" checked, you can perform this export/import but you need to do it for one table at a time.  For larger databases, this just isn't fun by any stretch of the imagination. ;)  A perfect solution is available at http://projects.c3o.com/files/3/plugins/entry11.aspx which leverages SqlBulkCopy within a very easy to use, stand-alone application to emulate the export/import capabilities of SQL Server 2000 in SQL Server 2005.  This is a serious life saver when needed.  A huge thanks to Fred Sobel for making this available.

Billy McCafferty


Posted 01-13-2008 7:52 PM by Billy McCafferty
Filed under:

[Advertisement]

Comments

Dave Donaldson wrote re: SQL Server 2000 Export Capabilities for SQL Server 2005
on 01-13-2008 10:52 PM

Actually, assuming we're talking about the same thing, SQL 2005 *does* allow you to import data without resetting identity values. When you start the import data wizard, you select source and destination databases. When you see the dialog box to select the source/destination tables and views, click the Edit Mappings button. That window has a "Enable identity insert" checkbox which does what you need. You just have to do that for each table you are importing.

Billy McCafferty wrote re: SQL Server 2000 Export Capabilities for SQL Server 2005
on 01-13-2008 11:02 PM

Thanks for clarifying Dave.  You're correct, you can use that approach on a single table at a time.  But when you need to transfer a couple hundred tables at once, this mechanism can get far greater than tedious.  Good candidate work for the Amazon Mechanical Turk, I suppose!

Sean Chambers wrote re: SQL Server 2000 Export Capabilities for SQL Server 2005
on 01-14-2008 8:40 AM

I got so frustrated with SQL 2005 and trying to port over old nightly import/export DTS packages that I just gave up. Working with SQL 2005 SSIS is a complete nightmare. I looked around the web for an open source alternative. I used Ayende's Rhino-ETL for awhile but there isn't any documentation for it which is a show stopper for me. I finally stumbled upon Pentaho data integration: http://www.pentaho.com/

This thing does A LOT of stuff. I had to recreate my imports as I couldnt import my DTS packages but it was really easy to setup export/imports with a ton of options. The best part is that it is open source! I would check it out if you are fed up with SQL 2005 SSIS as I was.

spikeless wrote re: SQL Server 2000 Export Capabilities for SQL Server 2005
on 01-14-2008 7:43 PM

What about the Microsoft Database Publishing Wizard available here: www.codeplex.com/.../View.aspx

Vlad Navazhylau wrote re: SQL Server 2000 Export Capabilities for SQL Server 2005
on 01-15-2008 1:22 AM

I used this utility for sometime now -->

www.codeproject.com/.../DataMigrationApplication.aspx

Billy McCafferty wrote re: SQL Server 2000 Export Capabilities for SQL Server 2005
on 01-15-2008 8:06 AM

Those two look useful and sufficient as well.  Thanks for the tips.

Mikey wrote re: SQL Server 2000 Export Capabilities for SQL Server 2005
on 02-18-2008 10:54 PM

Turn off "optimise for multiple tables"  and turn on "Enable identity Insert".  That works for me! :)

DORSON wrote re: SQL Server 2000 Export Capabilities for SQL Server 2005
on 10-03-2008 6:13 AM

DOES ANYBODT KNOW HOW TO EXPORT TABLES FROM SQL2005 TO 2000?

Web Radio wrote Web Radio
on 01-22-2009 8:07 AM

Andere haben Werbebanner in gratis Web Spaces vorgesehen.

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)