Should you do Full or Incremental Backups

Introduction

Backing up a database is an essential task for a DBA. It positively affects data integrity and accessibility. An image copy backup can be incremental or full. Because database and application requirements vary, it’s important to know the difference between the two backup types, so that you can use the most appropriate method to reach your stated goals. If you’re wondering should you do full or incremental backups, the short vague answer is probably both. The more detailed answers follow.

A description of a full image copy backup

When the database object is copied in its entirety and the image copy are backed up simultaneously, it’s called a full image copy backup.

A description of an incremental image copy backup

When data changes have occurred and those modifications are the only data that is backed up, it’s called an incremental image copy backup or differential backup.

>NOTE: A differential backup means the same thing as an incremental image copy backup.

Advantages of an incremental backup

The main advantage of an incremental backup is that it can be accomplished much faster than a full backup due to the smaller amount of data the system has to copy. It also leaves space that is more available whether the data is copied to a tape or storage disk.

Disadvantages of an incremental backup

A key disadvantage is that it takes longer to access the data that took less time to backup. Loss of productivity could result as well because sometimes a row will require repeated updates before recovery is completed.

Here’s an example of how that scenario would play out. On Tuesday at 3:00 a.m., you backup a database object by doing a full image copy. The next three days around the same time, you decide to backup the database object by running an incremental copy.

In order for the data to be completely recovered, you’ll need four copies in total: the three incremental image copies and the full image copy.

It would have been more efficient to backup the database object with a full image copy four nights in a row. That way, to completely recover the data, the most recent full image copy is all you’d need. The older backups that were created before the last one would be out of date.

A tool in DBMSs assists to DBAs

DBAs might be able to activate a feature in their DBMS that can recommend which type of backup is best for the situation at hand. DBAs should check the copy utility for the option to do either an incremental backup or a full backup. What’s more, limits can be set for how much data should change before either backup is run.

Those DBMSs that don’t offer that option, DBAs must rely on their experience with the databases and applications to determine when to backup their data and how to best get it done.

Here’s a tip for DBAs: If the change to data blocks are at least 30 percent, run a full image copy backup. Other factors come into play too, for example, how critical the data, its available needs, and the overall performance capability of the DBMS.

Backup recommendations

The term “small” is subjective because each DBMS perceives small differently. Complete full image copies work well with small databases.

One tactic is to run incremental image copy backups for databases that are large if they routinely have fewer changes when the backup time comes around. In situations where smaller batch processing windows get even tinier and the table space increases in size, incremental copying is a consideration.

>NOTE: Instead of counting rows, the data percentages is the figure to go by.

Some DBMSs won’t allow incremental copies. Not all, but some utility processes and operational tasks allow users to turn off logging. When logging is disabled, changes might still occur. Running a full image copy is needed to backup those adjustments.

DBMSs and merging

It’s likely that a merge utility is available if a DBMS is capable of incremental image copy backups. Merge many backups that are incremental and make one, or merge them into one full backup. It’s a good idea to follow a policy of always making a full image copy afterward to reduce recovery time.

Conclusion

Downtime can be shortened with advanced planning. Incremental image copy backups and full image copy backups are tasks that DBAs do to protect their organization’s data. However, if the data cannot be restored within a reasonable time after a system failure, those backup tasks become futile.

There are many options within the DBMS that are waiting to be utilized. Merging features, logging options, and more that help DBAs make the right decision on how they run backups.

You want to know should you do full or incremental backups? As we’ve discovered, it depends on the circumstances such as the business needs and the DBMS’s options for the restoration of data. Aim to raise productivity in recovery and lower downtime in a system failure event.