How do I recover when a SQL Server database crashes?

No database system can avoid a crash situation, even if you use a clustered, two-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 Master
Go
sp_configure ' allow updates ', 1
Reconfigure with override
Go
And then
Update sysdatabases Set status = 32768 where name = ' <db_name> '
Now, pray for the blessing of the gods, 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.