Database Recovery Techniques

Preventing data loss is one of the most critical issues involved in managing database systems. Data can be lost as a result of many different problems:

• Hardware failures
• Viruses
• Incorrect use of UPDATE and DELETE statements
• Software bugs
• Disasters, such as fire or flood

To prevent data loss, you can implement a recovery strategy for your databases. This article explains the traditional database recovery techniques that are out there.

If you are only concerned about backing up your WordPress blog then simply use a database backup plugin explained in the Must Use Plugins List article.

Full Database Backups

A very common backup strategy is to back up the whole database in a predefined time series (once each night, for instance). With such a backup strategy, it is possible to recover a database to the state it had when the last backup occurred. This strategy is implemented by using full database backups. A full database backup contains all data and database Meta information needed to restore the whole database, including full-text catalogs. When you restore a full database backup, it restores all database files yielding data in a consistent state from the time the backup completed.

Using Differential Backups

The main advantage of a full database backup is that it contains all the data needed to rebuild the entire database. But this advantage can also be a disadvantage. Consider a database where full database backups are performed each night. If you need to recover the database, you always have to use the backup from the previous night, resulting in the loss of a whole day’s work. One way to reduce the potential period of time that can be lost would be to perform full database backups more often. But this itself can be a problem. Because all data and parts of the transaction log are written to the backup device, it can be very time-intensive to make a backup. Also, you need a lot of storage space to hold these backups, and a full backup can decrease the performance of your database as a result of the large amount of I/O it requires. Wouldn’t it be better to perform one full database backup at night and only take backups of data changes made during the day? This sort of functionality is provided by the differential backup.
The differential backup stores only the data changes that have occurred since the last full database backup. When the same data has changed many times since the last full database backup, a differential backup stores the most recent version of the changed data. Because it contains all changes since the last full backup, to restore a differential backup, you first need to restore the last full database backup and then apply only the last differential backup.

Performing Differential Backups

Performing a differential backup is very similar to performing full database backups. The only difference is that you state in the WITH option of the backup that you want to perform a differential backup. The syntax of the BACKUP DATABASE statement to perform a differential backup of AdventureWorks to a physical device, overwriting other existing backups on the backup device, is as follows:

USE master;
GO
BACKUP DATABASE AdventureWorks
TO DISK=’t:\adv_diff.bak’
WITH INIT, DIFFERENTIAL;


Using Transaction Log Backups

With the combination of full database and differential backups, it is possible to take snapshots of the data and recover them. But in some situations, it is also desirable to have backups of all events that have occurred in a database, like a record of every single statement executed. With such functionality, it would be possible to recover the database to any state required. Transaction log backups provide this functionality. As its name suggests, the transaction log backup is a backup of transaction log entries and contains all transactions that have happened to the database. The main advantages of transaction log backups are as follows:

• Transaction log backups allow you to recover the database to a specific point in time.
• Because transaction log backups are backups of log entries, it is even possible to perform a backup from a transaction log if the data files are destroyed. With this backup, it is possible to recover the database up to the last transaction that took place before the failure occurred. Thus, in the event of a failure, not a single committed transaction need be lost.

Combining Transaction Log and Differential Backups

Another possible backup strategy is to combine full database, differential, and transaction log backups. This is done when restoring all transaction log backups would take too much time. Because restoring from a transaction log backup means that all transactions have to be executed again, it can take a great deal of time to recover all the data, especially in large databases. Differential backups only apply data changes, which can be done faster than re-executing all transactions.
To recover a database when you have a combined backup strategy, you need to restore the last full database backup, the last differential backup, and then all subsequent transaction log backups.

The Full Recovery Model

As mentioned before, you need to tell SQL Server in advance which backup strategy you plan to implement. If only full database and differential backups are used, the database has to be set to the simple recovery model. If you also want to use transaction log backups, the recovery model must be set to FULL (or BULK_LOGGED). The full recovery model tells SQL Server that you want to perform transaction log backups. To make this possible, SQL Server keeps all transactions in a transaction log until a transaction log backup occurs. When the transaction log backup happens, SQL Server truncates the transaction log after the backup is written to the backup device. In simple mode, the transaction log is truncated after every checkpoint, which means that committed transactions (which are already written to the data files) are deleted from the transaction log. Thus, in simple mode, transaction log backups cannot be created.
To set the recovery model to FULL, use the ALTER DATABASE statement again. The following code sets the recovery mode of AdventureWorks database to FULL:

USE master;
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO
To set recovery model to SIMPLE, use the following command
USE master;
GO
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO

Basically, try to be prepared so you don’t get caught with your pants down like this 🙂