Database security: How to recover when 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.) 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  Yuen Huang dioxins refers to blow the head allowance benzyl circular Huai Teman  also  Yan Zeng chiselled 鍪  dipped in the onion chiselled Xing, 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 up the first use of Master go sp_configure allow updates, 1 reconfigure with override going then update sysdatabases set STA TUS = 327The WHERE 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 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 DBCC sp_dboption, single user, true DBCC CHECKDB () 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 =--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, the 0 reconfigure with override GOCHECKDB may report some errors, the wrong data you may have to discard. CHECKDB there are several repair options, you can look at it, but in the end you may have to Repair_allow_data_loss, complete all the repairs. CHEKCDB does not complete all the repairs, we need to go further and examine each table with DBCC CHECKTABLE. 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. The commands and objects mentioned above are described in detail in Books Online, please note. The responsible editor Zhao Zhaoyi#51cto.com TEL: (010) 68476636-8001 give force (0 votes) to be tempted (0 Votes) nonsense (0 Votes) Professional (0 Votes) The title party (0 Votes) passing (0 Votes) Text: Database security: How to recover when SQL Server database crashes Back to network security home