Database Backups Basics

Types of Database Backups

There are four main types of database backups that DBAs perform. Some are more common than others.

Various factors such as data type, or even unique schemas within an environment can dictate the type of backups run.

Normal/Full Database Backups

When a normal or full backup runs on a selected drive, all the files on that drive are backed up. This includes system files, application files, user data — everything. Those files are then copied to the selected destination (backup tapes, a secondary drive, or the cloud). Following this, all the archived bits are then cleared.

Normal backups are the fastest way to restore lost data because all the data on a drive is saved to one location. The downside of normal backups is that they take a very long time to run, and in some cases, this is more time than a company can allow. Drives that hold a lot of data may not be capable of performing a full backup, even if they run overnight. In these cases, incremental and differential backups can be added to the backup schedule to save time.

A very important piece of a full or normal database backup is the consistency of the data. A DBA has a choice when executing this type of backup. They can shut the database down – referred to as a “cold backup” or leave it “open” while they perform this task, a “hot backup”.

This matters because oftentimes an organization simply cannot have their production database down and so it is left “on”. The implication of this is that while it is backing up, it is also editable meaning that changes in the data can occur during the backup. Changes occurring to the data while a backup is running requires the application of change logs during recovery.

Incremental Database Backups

A common way to deal with the long-running times required for full backups is to run them only on weekends. Many businesses run incremental backups throughout the week since they take far less time. An incremental backup will grab only the files that have been updated since the last backup. An incremental backup only contains changes since the most recent backup.

While incremental database backups do run faster, the recovery process is a bit more complicated. Here’s an example:

In this scenario, the normal backup runs every Saturday and an incremental backup is run nightly. Say that a file was updated Monday morning, but something happened to that file on Tuesday, one would need to access not only the weekly full backup from Saturday, but the incremental backups from Sunday and Monday, as well.

For one file, that’s not too complicated. However, should an entire drive be lost, one would need to restore the normal backup, plus each and every incremental backup run since the normal backup.

Differential Database Backups

An alternative to incremental database backups that has a less complicated restore process is a differential backup. Differential backups and recoveries are similar to incrementals in that these backups grab only files that have been updated since the last normal backup. However, differential backups do not clear the archive bit. This means that a file that is updated after a normal backup will be archived every time a differential backup is run until the next normal backup runs and clears the archive bit.

Similar to our last example, if a normal backup runs on Saturday night and a file gets changed on Monday, that file would then be backed up when the differential backup runs Monday night. Since the archive bit will not be cleared, even with no changes, that file will continue to be copied on the Tuesday night differential backup and the Wednesday night differential backup and every additional night until a normal backup runs again capturing all the drive’s files and resetting the archive bit.

A restore of that file, if needed, could be found in the previous night’s tape. In the event of a complete drive failure, one would need to restore the last normal backup and only the latest differential backup. This is less time consuming than an incremental backup restore. However, each night that a differential backup runs, the backup files get larger and the time it takes to run the backup lengthens.

Daily Database Backups

There is a fourth, less common form of backup, known as daily backups. This is usually saved for mission-critical files. If files that are updated constantly cannot wait a full twenty-four hours for the nightly backup to run and capture them, daily backups are the best choice. This type of backup uses the file’s timestamp, not the archive bit, to update the file once changes are made. This type of database backup runs during business hours but having too many of these files can impact network speeds.

Two Other Types of Backups

Remember when we said that there were four main types of backups? While that is technically true, here are two more types of backups that may be performed.

Physical backups and logical backups.

Physical

A physical backup involves copying all physical files that make up a database to a safe location, i.e. an external storage device. Entire tables can be backed up in this manner.

LogicalUnlike physical backups, logical backups copy data, not physical files, to some other location.  This allows very specific data to be preserved instead of entire database files.  Logical backup tools, like export utilities, allow data to be “selected” from the database, and stored in export files, which can then be imported into the same or different database.

RPO vs RTO

There are two key terms that are central to information (database) backups which must be understood; RTO and RPO. RTO stands for recovery time objective. RPO stands for recovery point objective.

These two concepts play an important role in business continuity planning.

A typical business continuity plan has three parts:

• Assessment
• Disaster Recovery Plan
• Return to Normal

Within the “disaster recovery plan” lies an organization’s backup strategy. Having a backup strategy that effectively balances RTO and RPO is a key part of business continuity planning.

RTO

RTO is how much time an organization can have their database down for before it damages the business.

RPO

RPO is how much data they can acceptably lose before damage is done to the business.

Why it Matters

Managing these two metrics is critically important to managing database backups.

Something that impacts RTO and RPO and to that point, backups as a whole, is the amount of data (data size) and the time it will take to back up the database. This determines the type of backup that an organization must perform. For Oracle databases as well as Microsoft SQL Server, MAS has the ability to schedule backups which will occur automatically at your set time. This enables organizations to plan their backups around their RPO and RTO goals consistent with the size of their data. This scheduling ability ensures that the backups run smoothly with as little interruption into business as possible while also protecting valuable data.

A Final Word

No matter how the backups are done and managed, they generally fall into one of the types described above.

Each type has advantages and disadvantages. Multiple database backup approaches can be used together to design a comprehensive server backup and recovery strategy.

A customized backup plan can minimize downtime and maximize efficiency.

To learn how Vendita MAS can help with your backup strategy, fill out a contact form here!