copying DB from one server to another

[rsoxhater]

I'm trying to copy an SQL server DB from my current host to the new one - I copied it by connecting through enterprise manager, selecting export, and then exporting it to the new server. Everything seemed to go fine, and the data is there, but I get this error:

Microsoft OLE DB Provider for SQL Server error '80040e2f'
Cannot insert the value NULL into column 'idDbSession', table 'emstore.einstein.dbSession'; column does not allow nulls. INSERT fails.
/productcart/pc/DBsv.asp, line 48


I checked with the shopping cart company, and this is what they said:

It looks like you did a migration over from another SQL server, and did not ensure that the identity fields were brought over also. Unfortunately, almost every table has an identity field, and the only way to fix this, is to do the migration again or manually go through and change all the datatype properties.

I believe the only 'valid' ways to move the DB from one SQL server to another is using the SQL Backup & Restore capability, or create a DTS (Data Transformation Service) package for the database.




How do I do this so the identity elements transfer as well?

 

[mrdenny]

The only way to get the identity info to come accross is to backup and restore, or to connect via Enterprise manager, and script the tables, then dts the data into them.

I would recommend the backup/restore method.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)