Possible problem using ss2k5's Import/Export & identities...

microsoft.public.sqlserver.tools

25 Oct 2006 2:56 AM

MrPCMan

I think there's a problem using the ss2k5 export tool. Let say for example
you have two databases - both are identical in structure (actually second one
is a restored backup of the first - just emptied out of all records using a
truncate of all tables) - let's called that database (b) (the empty one) and
database (a) with full records. Both databases have functioning foreign keys,
primary key, indexes, constraints and identities on 50% of all tables. Now
your task is to make an exact duplicate of database (a) to database (b) using
the ss2k5 import/export tool.

Automatic assumptions are that you are going to have to extract/drop all
foreign keys, primary keys and indexes from database (b) before starting then
using the import/export tool. You start using the tool and define all the
tables, views that you are going to bring over then using the default within
the tool - you begin using the tool to copy over tables from database (a) to
database (b). Of course, taking into consideration your automatic assumptions
above.

The problem occurs after the copy is finish. Data records that look like
this in database (a):

1 CA
2 MD
13 DC
24 VA

now looks like this in database (b):
1 CA
2 MD
3 DC
4 VA

The identities are still in effect and even I denote to turn them off before
the copy its still the same thing. The same occurs if your enable or disable
identities from the import/export tool before copy - still the same you get
in database (b) the following:
1 CA
2 MD
3 DC
4 VA

Now after investigating this I have found that there really a problem with
ss2k5 and a hotfix is needed. What I am asking everyone here - does anyone
else notice this same problem. Here is the support article:
http://support.microsoft.com/kb/911937/en-us?spid=2855&sid=global

I then followed the link to this page which describes all hotfixes available
after ss2k5's SP1: http://support.microsoft.com/kb/918222.

I am at build version number: 9.00.2047

This problem is REALLY A PROBLEM when you're fixing database issues and
trying to import/export data into new database structures. What you receive
is foreign key constraint errors that are affected because the keys are
re-numbered. THIS IS A MAJOR PROBLEM! On a single database build it may take
me 3 hours to build - searching for keys that are not really there!!!

Could someone from Microsoft please help me in the area?
If there is a fix please let me know if it needs the cumulative fix or just
a single fix will do?

Thanks for any and all help in this manner! I will keep check on this issue
daily!
Maurice