old database records reappearing after import to new database
Posted on 2007-11-21
I'm trying to migrate our website, including a SQL Server database, from our current webhost to a new webhost. The database was developed in SQLServer2000. My new webhost has SQLServer2005. Because it's the only way I can get to work, what I'm doing is exporting the entire database out of my old host onto my PC. Then I import the entire database from my PC onto my new hosts database server.
What I'm finding is that old records, or old versions of records, are reappearing in addition to what I think should be there. For instance, I have a "user" table that my old host shows as having about 8900 records and that appears to be correct to me. When i export this table to my PC, I check again at it still shows the same amount of records - 8900. When i then import this table up to my new host, i suddenly have about 33000 records for that exact table.
Looking at the data i see what appears like multiple copies of what should be single records. What's worse, I do have an indentity column on this table that assigns unique user ids yet so i'm seeing multiple records that show the same userid. it looks almost like old versions of records that I've modified are showing up... for instance, i see one record where I know i changed the status code of one of our internal users that quit last month and i see both the "current" version of that record with the correct code but also the "old" version of that record with the old code. I changed that code directly in Enterprise Manager.
Any ideas on what the heck is going on? Thanks!