Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to recover after a SQL Server database crash". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to recover after a SQL Server database crash".

No system can avoid crashing, even if you use Clustered, dual-computer hot standby. It is still impossible to completely eradicate the single point of failure in the system, not to mention that for most users, they cannot afford such an expensive hardware investment. Therefore, when the system crashes, how to restore the original valuable data has become an extremely important problem.

At the time of recovery, the ideal situation is that your data files and log files are intact, so you only need sp_attach_db to attach the data files to the new database, or copy all the data files (there must be master, etc.) to the original path when downtime, but this practice is generally not recommended, sp_attach_db is better, although a lot of trouble.

However, in general, when the database crashes, the system may not have time to write outstanding transactions and dirty pages to disk, so sp_attach_db will fail. So, count on DBA to have 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 can still be read, congratulations! You can go back to the state you were before the crash.

In general, there is no full-time DBA. If there is no available backup, it is more likely that the last backup was too long and led to unacceptable data loss, and your active transaction log is also in an unavailable state, that is the most troublesome situation.

Unfortunately, most database crashes are caused by the storage subsystem, in which case it is almost impossible to have available logs for recovery.

Then we'll have to try these plans. Of course, it is required that at least your data files exist, if the data files, log files and backups are gone, don't call me, you can go to the roof and sing "God, help me".

First of all, you can try sp_attach_single_file_db, try to recover your data files, although it is unlikely to recover, but if the database happens to execute a checkpoint, it is possible to succeed.

If you are not lucky enough to touch the lottery, the most important database is not attach as you expected, don't be discouraged, there are other plans.

We can try to create a new log. Setting the status of the database to emergency mode,sysdatabases to 32768 indicates that the database is in this state.

However, the system table can not be changed casually, set it first.

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 gods and Buddhas all over the sky and re-create a log file. The chances of success are still quite high, and the system will generally recognize your newly created log. If no mistakes are reported, you can breathe a sigh of relief now.

Although the data is restored, don't think that even if the work is done, the ongoing transaction must be lost and the original data may be corrupted.

Restart SQL Server first, and then check your database.

First set it to single-user mode, and then do

Dbcc sp_dboption, single user, true

DBCC CHECKDB ()

If there is no big problem, you can change the database state back, and don't forget to turn off the modification option for the system table. Update sysdatabases set status = 28 where name =, of course, your database status may not be this, change it to the appropriate value. It can also be used

Sp_resetstatus

Go

Sp_configure allow updates, 0

Reconfigure with override

Go

Some errors may be reported during checkdb, and you may have to discard the wrong data.

Checkdb has several repair options, so you can see for yourself, but in the end you may have to REPAIR_ALLOW_DATA_LOSS and complete all the fixes.

Chekcdb does not complete all the fixes. We need further fixes. Check each table with DBCC CHECKTABLE.

The list of tables can be obtained in sysobjects. Find out and check that OBJECTPROPERTY is all of IsTable. This can basically solve the problem. If errors are also reported, try to select into the data to another table to check.

When all this is done, recreate all indexes, views, stored procedures, triggers, and so on. Maybe DBCC DBREINDEX can help you with something.

Thank you for reading, the above is the content of "how to recover after the SQL Server database crash". After the study of this article, I believe you have a deeper understanding of how to recover after the SQL Server database crash, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.