What is Database Recovery and How does it Work?

Modern computing relies on databases to store vast amounts of critical data, and many businesses and industries rely on this to support their operation and help drive critical business decisions. But just like any other technology, it may suffer from glitches and failures that could result in data loss.   

This is where data recovery comes into play. Many business owners heavily rely on data recovery when encountering significant data loss since their business can come crashing down once the data recovery software they are using does not function as they should.

Database recovery is the process of returning a database back to normal operations after some failure. The exact steps needed for recovery depend on what kind of failure and on the recovery strategy (see below). Database recovery can be done either automatically or manually. 

Manual recovery requires querying the database administrator (DBA) about what to do after a failure; automatic recovery has its own intelligence built-in and is usually able to determine what needs to be done without any explicit guidance from an administrator. Automatic recovery is therefore much more common.

Automatic recovery makes use of information stored in system tables, such as data file checkpoint records, undo logs, redo logs, and so on. The DBA puts special markers into these files when making changes and they contain enough information for SQL Server to undo those changes if needed.

Additionally, there are log sequence numbers (LSNs), which allow the system to determine the order in which log records need to be applied.

How Does Database Recovery Work?

SQL Server uses an Undo/Redo strategy, which involves two temporary files: 

The undo file is used for undo operations and the redo file for redo operations. They are usually very small. These files store all kinds of information that may change as a result of transactions such as row versioning (to avoid read uncommitted anomalies) and some data pages. If needed, SQL Server will use those temp files to recover your data after a crash by rolling them back into the original data file(s). This process can take quite a while though; depending on how big the transaction log is and on how much activity is going on.

How much time is needed for recovery?

It really depends on the kind of failure and what database recovery strategy you use: full or bulk-logged. 

The following summarizes how much time manual and automatic recovery can take.

Recovery Process Duration (in minutes); Type of process; Description; Full Automatic 

The system performs a roll-forward recovery that restores all committed transactions from the transaction log and then rolls back uncommitted transactions in the same order as they were applied to the affected data files Redo thread allocated by Undo/Redo Log Space Determines how much redo SQL Server will allocate for each undoes space to be used during automatic recovery Operations allocated for undoing.

For example, how many threads will be used for undoing In-Memory OLTP transactions Bulk-Logged Automatic SQL Server performs a bulk-logged recovery as if the database were in simple recovery mode.

No redo or undo is performed for data page modifications made by bulk operations after the bulk checkpoint was issued Operations allocated for undoing For example, how many threads will be used for undoing In-Memory OLTP transactions Full Manual The system first performs a rollback of all uncommitted transactions and then restores all committed transactions from transaction log Backup/Restore process takes longer at night when there are fewer users accessing the database How long does it take? It depends on how big your transaction log is and how much activity is going on.

If you use Bulk-Logged Recovery, the time needed for recovery can be significantly lower. The bigger it is though and the more activity there is, the longer it takes to recover. If you use Full Recovery, there’s no real difference between during or after hours: you always take a big hit.

How can I speed up database recovery?

The best option is to avoid having to do it at all! One way to accomplish this is by using In-Memory OLTP (formerly code-named Heaton). As of SQL Server 2016, you’ll be able to get all of its benefits without any need for special hardware upgrades.

All your tables and indexes will live in memory and we won’t have to read from the disk while processing updates and reads as we do today. The impact on your recovery times will be significant:

One effective way to speed up database recovery is to do incremental backups consistently. This helps the database get updated so recovery can be faster. Also, investing in a high-performance storage system will be very helpful if your business is growing fast. This can speed up reading and writing data and reduce recovery time.

SQL Server 2016 update to In-Memory OLTP 

If you can’t use In-Memory OLTP like if you need to support older versions of SQL Server (2008 and earlier), the next best thing is to back up your databases regularly so that you have a valid restore point in case something unexpected happens.

This way, you’ll only have to do a full recovery during the day when users are active or after hours so there won’t be any real impact on your business! If even this is not an option for you, consider adding more transaction log files to your database(s) using trace flag 25.

This way, the system will be able to create the log files faster and switch them out faster as well. This can significantly speed up your recovery process by reducing the impact of filling the transaction log on the Undo/Redo Threads (which are limited in number) and doing less work during backup (and therefore using fewer resources).

Conclusion:

Any business should have a plan for incidents like a sudden failure of computer systems resulting in loss of data. This is not something you can wish not to happen because some things are inevitable. 

Database recovery strategies are crucial in any organization’s disaster recovery plan. This is essential to ensure business continuity, so taking steps to protect databases should always be addressed. It would be prudent to invest in tools to help you with this and acquire the help of experts, so you don’t grasp at straws when it happens.

The bigger your database(s) are, the more time it will take to recover them. For Full Recovery, this is not really a problem since users won’t be active during recovery.

For Bulk-Logged Recovery though, you might want to consider using SQL Server 2016 or updating your old version of SQL Server to support In-Memory OLTP or adding more transaction log files so that recovery can complete faster after hours when no one is accessing the system.