Choosing a Database Backup Method

Organisations from any industry, big or small, rely heavily on the database as the spine of an IT infrastructure. This infrastructure is used for automated data collection, processing, analytics, and all other data related systems.

Often the IT Administrators or DBA’s are tasked with database backup planning and execution duties. Many database management systems come with backup management which can be configured according to the organisation’s policies.

The question is, however, how many of these companies have a backup strategy to use to configure these backup management solutions?

In this blog, we will show you three backup methods which are commonly used and how an administrator can configure them to create the best plan for their organisation.

A good backup plan minimizes downtime and ensures fast operational recovery. Here are the most common backup methods,highlighting pointers on where to start creating a basic backup strategy planning.

What is a Database Backup?

Before we start talking about backup methods, let’s talk about the basics. A database backup is the process of creating a duplicate instance of a database at a specified interval. This can be done manually by a DBA, by using a backup script, by using an automated service, or by using database management software. The backup taken can then be used during the for a partial or a full database recovery.

Database Backup Methods

Database backups can be done in three common ways; full, incremental, and differential. To minimize downtime, organizations should have a good combination of at least two of these methods implemented. Let’s look at the basic idea behind each of these ways and try to understand why it’s best to have the combination of them in your backup policy.

Full Database Backups

A full backup (as its name implies) is a method to copy all data including user data, system files, and configuration files from one location to another. Since it makes a copy of everything, it’s a time consuming process. However, this type of backup takes a minimal amount of time recovering the data.

This backup method is typically done periodically for large data centres, however, if the database is a small one, this type of backup could be done on a daily or even a more frequent interval.

Although it takes time for a full backup, this method ensures all data is at one location from a specific timestamp. Restoration time, therefore, is faster though there might be some loss of data in time but the operation can be resumed with a good amount of data. This may occur, provided the backup has been scheduled periodically and at reasonable time intervals (especially if this is the only method employed in the organisation).

Incremental Database Backups

Incremental backups make a copy of updated or newly created files since its last normal backup in an iterative manner. This type of backup compares the state change since the last incremental backup. This type of backup is best when the restoration requires you to store recent changes in smaller chunks.

Incremental means the backup is done in a shorter time with a smaller amount of data, hence this backup method is faster and requires less space. If the entire data set is lost, both backups (i.e full and incremental backup data) are required for a full database restoration.

Differential Database Backups

This backup method stores the recent updates since the last full backup, very similar to the incremental backup. Differential backup stores the cumulative copies of changes compared between the first backup and daily transaction changes. Once a differential backup reaches a certain size a full backup should be triggered, otherwise the backup copies will exceed the original baseline backup. During restoration, only a full backup is required, as it has been updated regularly with the differential backup files. There is a full backup with recent changes, but this method relies heavily on the network bandwidth to check the recent files and of course more space.

Pros and Cons of the Database Backup Method

The three database backup methods each have their own advantages and disadvantages to be considered while preparing a good database backup strategy for an organisation.

A full database backup takes longer to backup and needs large storage space. If the organisation considers to do a frequent full back , a retention policy would be necessary to know how to manage and archive the older backup copies. A full backup can be done while the database process is still running or it may require a downtime for a full backup . This, of course, depends if the organisation is able to have downtime. At any time , during a recovery period , the full backup will be the best option for a fast and safe recovery.

When an incremental backup is done frequently, it runs faster and requires a smaller storage space. It also does not need high computing resources, as it takes a copy of the new transactions. Ideally, having an incremental backup in place is important to ensure you have any recent changes. This would ensure the full system image and timely activity data if a restoration is needed. Of course, the disadvantage of the incremental backup is the potential need to have both full and incremental backups for a proper recovery. It might be time-consuming to have this pieced together during the recovery point, but it’s still a lot faster than differential backups.

Differential backups are faster when compared to the full backup method. Restoration is faster as only the last full backup and differential backup sets are required for recovery. There are two perspectives to consider when it comes to space management. Differential backups do save space (as it only stores the last changes) but progressively it does require a larger storage space compared to the incremental backup method. A full backup must be available before starting a differential backup. If any subsequent differential backup fails, the recovery process will fail as well. This method always compares the changes with the full backup, hence it requires high network bandwidth.

Conclusion

It’s best to have at least two types of database backups available at all times for data protection. A set of full backups is always best to have to handle any full-recovery scenarios. Full and Incremental backups would be a good combination to have (the full backup copy to hold the recent transactions).

A full backup should be scheduled weekly on an external device. Ideally, also having an incremental backup in place to have the recent changes. This would ensure the full system image and timely activity data in any case of restoration, of course you would need a tool in merge the full and incremental backup sets during restoration.

Having multiple backups are important, however, it’s better to have a pre-planned backup strategy in place. This avoids any unnecessary downtime, especially if the goal of your company is to have 99% availability.

Backups don’t provide a fault-tolerant or zero downtime. It is not wise to have many backups running at shorter intervals in the hopes that this would save you from downtime. This method is rather a costly affair (especially if you are running an on-premise backup) as you would need to spend money on computing resources, storage, manpower, and of course, a sophisticated tool.

To avoid paying for some of these requirements, consider cloud backup solutions (Like Backup Ninja!) It provides hassle-free backup management, without the need for secondary storage, backup media mobility cost, all you need is a good network, a great cloud solution provider, and a good backup strategy in place.