No database system can avoid crashes. Even if you use Clustered, dual-machine hot backup... you still cannot completely eliminate single points of failure in the system, not to mention that most users cannot afford such expensive hardware investment. Therefore, when the system crashes, how to recover the original valuable data becomes an extremely important issue.
During recovery, the ideal situation is that your data files and log files are intact, so you only need to sp_attach_db to attach the data files to the new database, or all the data during shutdown It is okay to copy all the files (must have master, etc.) to the original path, but this approach is generally not recommended. sp_attach_db is better, although it is more troublesome.
However, when the database crashes, the system may not have time to write unfinished transactions and dirty pages to the disk. In this case, sp_attach_db will fail. So, let's hope the DBA has a good disaster recovery plan in place. According to your recovery plan, restore the latest full backup, incremental backup or transaction log backup, and then if your active transaction log is still readable, congratulations! You can revert to the state before the crash.
General units do not have a full-time DBA. If there is no backup available, it is more likely that the latest backup is too old, resulting in unacceptable data loss, and your active transaction log is also in an unavailable state. , that is the most troublesome situation.
Unfortunately, database crashes are generally caused by the storage subsystem, and in such a situation it is almost impossible to have available logs for recovery.
Then you have to try these solutions. Of course, it is required that at least your data files exist. If the data files, log files and backups are gone, don't look for me. You can go to the rooftop and sing "God, save me".
First of all, you can try sp_attach_single_file_db and try to recover your data files. Although the recovery is unlikely, it may still be successful if the database happens to execute a checkpoint.
If you are not lucky enough to win the lottery, and the most important database is not attached as you expected, don’t be discouraged, there are still other solutions.
We can try to re-create a log. First, set the database to emergency mode. If the status of sysdatabases is 32768, it means that the database is in this state.
However, the system table cannot be changed casually. To set it up, first
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
and then
update sysdatabases set status = 32768 where name = '
Now, pray for the blessings of the gods and Buddhas in the sky and re-create a log file. The chance of success is still quite high, and the system will generally recognize your newly created log. If no errors were reported, you can breathe a sigh of relief now.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
