How to recover when a SQL Server database crashes

server| Recovery | data | Database ANY database system can not avoid the collapse of the situation, even if you use the clustered, dual-machine hot standby ... It is still not possible to completely eradicate the single point of failure in the system, and for most users, this expensive hardware investment cannot be sustained. So, when the system crashes, how to restore the original valuable data becomes an extremely important problem. At the time of recovery, ideally, your data files and log files are intact, so you just need to sp_attach_db, attach the data files to the new database, or in the downtime of all data files (must have master, etc.) all copy to the original path under the line, However, generally do not recommend such a practice, sp_attach_db better, although a lot of trouble. However, when the general database crashes, the system may not have time to write the unfinished transactions and dirty pages to disk, such a situation sp_attach_db will fail. Then, expect the DBA to make a good disaster recovery plan. According to your recovery plan, restore the latest full backup, incremental backup, or transaction log backup, and if your active transaction log is still readable, congratulations! You can revert to the state before the crash. The average unit is not a dedicated DBA, if there is no backup available, it is more likely that the last backup time too long to cause unacceptable data loss, and your active transaction log is also in an unusable state, that is the most troublesome situation. Unfortunately, the general database crashes are caused by the storage subsystem, and it is almost impossible to have the logs available for recovery. Then we have to try these plans. Of course, is to require at least your data file is present, if the data files, log files and backups are not, don't look for me, you can go to the roof to sing "God, help me." First of all, you can try sp_attach_single_file_db, try to restore your data file, although the possibility of recovery is not very likely, but if the database just executes a checkpoint, it is possible to succeed. If you don't have the luck to touch the lottery, the most important database is not as attach as you would like it to be, don't be discouraged, or a different plan. We can try to recreate a log, first set the database to emergency mode,sysdatabases status of 32768 means that the database is in this state. However, the system table can not be arbitrarily changed, set the first use of Mastergosp_configure ' allow updates ', 1reconfigure with Overridego and then update sysdatabases setStatus = 32768 where name = ' <db_name> ' Now, pray for the blessing of the Gods of the sky, to re-establish a log file. The chances of success are still quite large, and the system will generally recognize your newly created journal. If you don't report anything wrong, you can breathe a sigh of relief now. Although the data is restored, but do not think that even if the matter is completed, the ongoing transaction must be lost, the original data may be some damage. First restart SQL Server, and then check your database. First set to Single-user mode, then do dbccsp_dboption ' <db_name> ', ' Single user ', ' true ' DBCC CHECKDB (' <db_name> ') If there is no big problem, you can change the database status back, remember to turn off the system table modification options. Update sysdatabases SET status = the WHERE name = ' <db_name> '-of course your database state may not be this, change yourself to the appropriate value. You can also use sp_resetstatusgosp_configure ' allow updates ', 0reconfigure with OVERRIDEGOCHECKDB when you may report some errors, these wrong data you may have to discard. CHECKDB has several repair options, look at it yourself, but in the end you may have to use Repair_allow_data_loss to complete all the repairs. CHEKCDB does not complete all the repairs, we need to do a further fix, and use DBCC CHECKTABLE to check each table. The list of tables can be found in the sysobjects, the objectproperty is the istable of all find to check it, this can basically solve the problem, if you also report the error, try to put the data select into another table to check. Once all of this is done, rebuild all indexes, views, stored procedures, triggers, and so on. DBCC Dbreindex may be of some help to you.