Using bcp to change sort order

[Luvsql]

I am using the following script to bcp out all my tables:

select 'bcp "dbname..' + name + '" out ' + name + '.out -e ' + name + '.err -c -b 1000 -U sa -P password -t "|" -S SERVERNAME -r "#EOR#\n"'
from sysobjects where type = 'U' order by name

I then save the scripts into a bat file.

I then run this to bcp in:

select 'bcp "dbname..' + name + '" in ' + name + '.out -e ' + name + '.err -c -b 1000 -U sa -P password -t "|" -S SERVERNAME -r "#EOR#\n"'
from sysobjects where type = 'U' order by name

I then save the scripts into another bat file.

I run the first bat file to export out all data (only takes about an hour for 5Gb database). I then create the database on the new server and run the second bat file.

Everything works fine, except some tables do not get updated and none of the .err files contain any error or inforamtion.

I tried using this:

Select name, rows from sysindexes
where indid in (0,1)
order by name

to figure out what tables did not get all the information imported, but it appears my larger tables have less records (ie the sysindexes are less before and more after because I am bcping in all the data at once).

Is there anyway to verify the total number of records in every table before and after (instead of relying on the sysindexes as they will probably not match)?

 

[MDXer]

create an info table something like ZZ_Misc_Load_Info with a Start_Date_Time an End_Date_Time Start_Rec_Count and End_RecCount.

Prior to the BCP process do a select Count(col) from Table. Then do it again after the BCP, write this to your load info table then you can more easily judge where the issues are.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck