Any database system is unable to avoid crash, even if you use Clustered, double-machine hot spare ... still can't completely eradicate single-point failure in the system, not to keep this expensive hardware investment for most users. Therefore, when the system crashes, how to restore the original valuable data is an extremely important issue.
When recovery, the most ideal situation is that your data files and log files are intact, which only needs sp_attach_db, attaching the data file to the new database, or put all data files (must There are MASTER, etc.), all COPY is OK, but it is generally not recommended, sp_attach_db is better, although there are many troubles.
However, when the general database crashes, the system is not necessarily to have time to write unfinished transactions and dirty pages, such that SP_ATTACH_DB will fail. So, it is expected that DBA has developed a good disaster recovery plan. Follow your recovery plan, restore the latest complete backup, incremental backup or transaction log backup, and then if your event log is read, congratulations! You can restore the status before the crash.
The general units are not full-time DBA. If there is no available backup, it is more likely that the last backup time is too long and the unacceptable data loss, and your activity log is also unavailable, that is The most troublesome situation.
Unfortunately, the general database crash is caused by the storage subsystem, and this is almost impossible to have available logs for recovery.
So just try these programs. Of course, it is required to require at least your data files, if there is a data file, log files and backups, don't find me, you can go to the top of "God, save me."
First, you can try sp_attach_single_file_db, try to restore your data file, although the possibility of recovery is not large, but if this database just implements a checkpoint, it is possible to succeed.
If you don't have a lot of luck with lottery tickets, the most important database doesn't have the attach you expect, don't be discouraged, or something else.
We can try to re-establish a log, first set the database to EMERGENCY MODE, STATUS for sysdatabases to 32768, indicating that the database is in this state.
However, the system table cannot be changed, set up
Use master
Go
sp_configure 'allow updates', 1
Reconfigure with override
Go
then
Update sysdatabases set status = 32768 where name = '
Now, pray for the blessing of the Buddha, re-establish a log file. The opportunity for success is quite large, and the system generally recognizes your newly established log. If there is no mistake report, you can make an angry now.
Although the data is recovered, don't think things have been completed, the ongoing transactions are definitely lost, and the original data may also be damaged.
First restart SQL Server and check your database.
First set it to single user mode, then do DBCC
SP_DBOPTION '
DBCC CHECKDB ('
If there is no big problem, you can change the status status and remember to turn off the system table's modification option.
Update sysdatabase set status = 28 where name = '
Go
sp_configure 'allow updates', 0
Reconfigure with override
Go
When checkdb, there may be some errors when checkdb, these wrong data, you may have to discard it.
Checkdb has several fix options that look at it yourself, but in the end you may still have to use Repair_Allow_Data_loss to complete all fixes.
CHEKCDB does not complete all repairs, we need further fixes, check each table with DBCC CheckTable.
The list of tables can be obtained in sysobjects, and the ObjectProperty is all ISTABLE to find it. This can basically solve the problem. If it is also reported, try to check the data SELECT INTO to another table.
After all of which are finished, re-establish all indexes, views, stored procedures, triggers, etc. DBCC DBREINDEX may help you some busy.
