We had a client run across some issues recently when they exported their SQL Server 2005 database from their old host and imported it into a new database at OrcsWeb. After spending quite a bit of time working with the client on why the application wouldn’t work, we tracked it down to a page on the site that tried to do an insert but was missing a key field. The client assured us that the same code worked elsewhere, so we continued troubleshooting for them. After a while we came across the idea to do a full audit and compare of their database hosted with OrcsWeb and the one at the old host. Well, come to find out, the databases weren’t the same.
But… wait… an export and then import were done directly through SQL so they should match! Yeah, you would think so, but there are apparently issues with SQL Server (at least in version 2005) where it can drop identity fields.
So with no identity set on certain fields in the new database, SQL Server was expecting that value to be provided. Since it wasn’t, the code barfed.
Here’s a link to a post someone else wrote about the issue:
The workaround would be to get a full backup from the one server and then perform a full restore on the new server – that’ll assure the tables, data, and *all* settings are brought across for the migration.
I hope this helps someone else. Happy hosting!
