How do I recover when a SQL Server database crashes?

server| Recovery | data | database

Work is over, this afternoon is free, write an article, may be helpful to everyone:

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.

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.
Set up Single-user mode first, then DBCC
sp_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_resetstatus
Go
sp_configure ' allow updates ', 0
Reconfigure with override
Go

CHECKDB may report some errors, and you may have to discard the wrong data.
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.

Then you can brag about your great feats to boss, by the way a small mention of the demand for a raise, if (very likely) not succeed, also had to go home to sleep: ' (
Remember the next time don't forget to do a backup Oh ~

The commands and objects mentioned above are described in detail in Books Online, please note.
Thank for your reading.