I've a database on a very old Computer (Windows xp, SQL server 8.0). Yesterday, the database has been marked as suspected, I've put it to the emergency state. So now the database is Read-Only. The only way to make it functional again is make a copy of its to another database. Previously, I use Backup to save the database to a File and restore to other database but now, the database is in Emergency mode so that this way is not working. I've tried to import/Export to other database but there is one problem, some key in some the table of the old Database was mark as Identity Specification and it will automatically generate. But now the new database is not mark as Identity Specification and the Null error is generate.
So why the import/export data doesn't make a exact copy of the old database to the new one? And how to make a exact copy to the database?
Best Answer
The import/export wizard doesn't copy a lot of stuff, the identity property on columns is just one of the things that it doesn't copy.
If you don't have a functional backup you'll want to manually script out all the objects from the database first, then copy the data using the import/export wizard.
Once your new database is up and running you'll want to look at setting up regular database backups so that if this happens again the database can just be restored from the backups.
You should also keep in mind that SQL Server 2000 has been end of lived and all support for the product is gone. Upgrading to a newer version of SQL Server would be a good idea.