Backup and Recovery in Database Systems

Introduction

This article is intended to cover one of the most critical responsibilities of database administrators in order to maintain data integrity. I will explain with some examples the main concepts of data backup and recovery in different database management systems.

We can find several scenarios of database failures such as media failures that occur when a server component fails and the content of data files cannot be read or it's corrupted. Then, the database administrator must perform the recovery process in order to restore the corrupted data files. We can find non-media failures such as process failure, instance failure and user error.

Before developing a recovery plan, we need to answer ourself some questions-

• What's the most important data?
  
• How much data can we lose? The less data we can afford to lose, the more often we should back up the data. For example, if you afford to lose data in a period of 24 hours; then, depending of database size, an acceptable solution is to do full backup every day. But, if you can not afford to lose data in a period of 20 minutes, then you must do transaction log backups every 20 minutes. You can consider other fault-tolerance solutions such as log shipping, database mirroring, RAID mirroring, archive the data files off-site to other network or tape device.
  
• How much downtime is acceptable to your business? How much money does your business lose for each hour of downtime? How much time is acceptable from the downtime to everything is running again? The solution is to have redundant data across several database systems using techniques such as replication, log shipping, database mirroring and failover clustering.

Now let's talk about the different methods of backup in SQL Server 2005. We can find three methods: full, transaction log and differential.

The full backup makes a full copy of the database including all changes and transaction logs as of the point of time when the backup operation ends. It's a time-consuming process.

Transaction log backup saves the transaction log's activities that have occurred since the last full or transaction log backups. After that, the transaction log's inactive portion is truncated in order to remain the transaction log files' size as small as possible.

Differential backup saves all the data and transaction logs that have changed since the last full backup. This type of backup generates files smaller than the full backup.

In order to perform a backup, you need to use the backup database statement. Please, refer to SQL Books Online.Let's do a full database backup of the AdventureWorks database to a file (see Listing 1).

name = 'Backup for Febrary, 2008, Week 2';

Listing 1.

If you have a very large database, you can use up to 64 backup devices (tapes, files) in the backup operation in order to stripe the backup stream across the devices by written to the devices proportionally and simultaneous (see Listing 2).

name = 'Backup for Febrary, 2008, Week 2';

Listing 2.

You can also use logical devices which are references to physical backup devices. The sys.backup_devices catalog view lists all the logical devices of your database system.

To add a new backup device definition (see Listing 3)

exec sp_addumpdevice 'disk', 'AdventureWorks_Backup', 'c:\temp\AdventureWorks_2008_Feb.bak';

Listing 3.

Now, let's backup the AdventureWorks database to the AdventureWorks_Backup logical device (see Listing 4).

backup database AdventureWorks
to AdventureWorks_Backup;

Listing 4.

If you want to delete the logical device, use the stored procedure sp_dropdevice (see Listing 5).

Listing 5.

If you want to delete physically the .bak file as well, you need to set the DELFILE option.

In SQL Server 2005, you can also mirror a database, transaction log, files and filegroups by creating redundant copies of these objects.

Transaction logs are very important as well as the data files because they record the transaction operations done over the database system. Thus, it's very important to backup the transaction logs. Notice that a transaction log backup cannot be executed until the database has a full database backup (the database must be set to FULL or BULK_LOGGED recovery model). When the process is over, then the inactive portion of the transaction log is automatically truncated (see Listing 6).

Listing 6.

Finally, let's explain how to do a differential backup of the AdventureWorks database. A differential backup allows to back up all the data changes from the last full database backup (unless transaction log backup that back up data changes from the last transaction log backup or full database backup).

In order to perform this type of backup operation, we use the same backup database statement but we add the differential keyword.

Finally, we're going to restore the database from a backup using the restore statement. Unlike the backup operation, restore operation is not only (all users must be disconnected before the restore process begins). It makes sense because this process is executed when the system fails. For more information about the restore statement, please refer to the SQL Books Online. This statement has a lot of options.

Let's do some changes to the AdventureWorks database and the restore the database using the full database backup (Listing 7).