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