I developed a document tracking database and front end C#/.NET app on my local machine (SQLServer Express and VS Express). Once it was working and usable I had our IT branch install the database (SQL Server Express) on the main server, and pointed the app to it instead of my local version. I then used bcp to export from my box and import to the server. I noticed that the parent child relationships are now all broken. Each table has an identity column called UID, which is referenced in child tables. But the UIDs did not copy as they were on my machine, rather they were all recreated upon insert. I have not found any documentation on the proper way to handle this, but now the data is basically unusable. I can easily redo the BCP, but can anyone point me to my mistake?
Here is what I used for the export (both the data and format just in case):
bcp DocControl.dbo.Documents out Documents.dat -n -S MYMACHINE\SQLEXPRESS -T bcp DocControl.dbo.Documents format nul -T -n -f Documents.fmt -S MYMACHINE\SQLEXPRESS
And for the import (also, before import I had duplicated the schema, so the tables, keys and stored procedures were all recreated ahead of time, but without the data):
bcp DocControl.dbo.Documents in Documents.dat -n -S SERVER\SQLEXPRESS -T
This is just for one of the tables, but the other tables were done exactly the same way. Now the Documents table UID starts at 1 and goes up, whereas previously it started at something like 380 and went up from there. So the referring IDs in child tables no longer point to the right parent.