Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 28 '13 at 15:46

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

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.

share|improve this answer
    
the OP has SQL server 8.0 .. which is dead long time :-) I presume that it might be a typo as the tag is sql 2000. –  Kin Apr 29 '13 at 0:03
1  
SQL Server 8 is SQL Server 2000. SQL Server 2000 is the marketing name, SQL Server 8 is the version number. Extended support for SQL Server 2000 ended this month (April 2013). –  mrdenny Apr 29 '13 at 12:23
1  
Really? DAMN GOOD TIMING for the database to crash ;) a month after extended support. Artificial intelligence? –  TomTom May 28 '13 at 11:28
add comment

If the new database doesn’t have identity specifications then there shouldn’t be any issues with importing data. If it does have identity specification on some columns then you can temproarty disable it like this

SET IDENTITY_INSERT {table name} ON

share|improve this answer
    
SET IDENTITY ON applies to a table, not a database –  Pondlife Mar 20 '13 at 13:22
    
Yeah, you're right. My bad. It’s fixed now. Thanks @Pondlife –  Igor Voplov Mar 22 '13 at 9:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.