A necessary part of ensuring your business’s continuity is the ability to restore your databases from backups. Unfortunately however, it’s not uncommon for IT administrators to do the bare minimum required in terms of backup, and not consider important factors such as viability, integrity, reliability and security.

While the topic of database backups might seem difficult from the first glance, it’s really more about the specific database type that you’ll have to work with. While smaller companies tend to delegate their backup management responsibilities to someone with other jobs in the company, a lot of larger companies tend to employ a specific, dedicated person for all of their database backup management matters. This person’s position is often called “database administrator” (or DBA).

Database administrators have a number of responsibilities, but one of the biggest ones is to be ready for the possibility of any kind of failure that might cause a data loss, be it media failure, hardware failure or software failure. Recovering databases whenever needed is therefore likely to be one of their responsibilities. Two of the most important points related to the backups in the context of anything happening is their integrity and the time period that it will take to restore backups.

In general,  backup solutions can be separated into two categories: solutions that  work with as many different data sources as possible, and solutions that focus only on a specific number of database types and work with those only. Bacula is a participant of the first group, offering compatibility with an especially wide range of  different backup targets, including SQL databases.

In working to prevent data loss and failures from happening or at least attempt to mitigate their consequences, it is not uncommon for database administrators to employ various methods and techniques, including:

  • Creating disaster recovery plans.
  • Improving the overall effectiveness of backup management operations.
  • Testing database backups regularly.
  • Developing detailed backup plans.
  • Updating their knowledge about database backups, OS backups, and so on.

The points above are fairly standard. However, there are a lot of different database types and each of them has their own software list, feature list, and even some specific details that might not be present in other backup types. In this article we  describe three of the popular database types: MySQL, Microsoft SQL Server and PostgreSQL.

MySQL database backup

MySQL as a database type is a very popular data repository for many different applications, thanks to both its efficient SQL architecture and its low-cost licensing rules. The popularity of MySQL as a whole has led to a wide range of solutions that use or work with MySQL databases in some way or other, including both premium backup solutions and free, open-source ones.

A properly installed MySQL backup system should be capable of preventing an organisation from losing all of its data if some sort of accident or a failure occurs. Backup is not only for failures or accidents like a data breach or a natural disaster, it’s also highly effective when it comes to compensating for problems that might arise in the process of data migration.

As with many other SQL databases, the actual database backup process cannot be performed without stopping the entire database, since backing up a live database can easily corrupt backups and create inconsistencies. Understandably enough, having entire databases stopping is often very inconvenient for businesses, so keeping the database downtime as short as possible is often an important goal of the database admin.

There are several different approaches to MySQL database backups, both with and without using third-party backup-specific applications. Some of the most common ones include:

  • Automatic MySQL backup in Linux
  • mysqldump
  • An entire backup using PHP
  • Xtrabackup
  • cold, file based backups
  • Shell script for MySQL backups in Ubuntu
  • Using phpMyAdmin for MySQL backup purposes

You can learn more about the differences between different MySQL backup methods and different free database backup software in this article.

Microsoft SQL Server database backup

Microsoft SQL server is also a widely popular database management system. There is, accordingly, a broad range of backup solutions for Microsoft SQL databases. Of course, all the reasons for backing up systems remain the same.

Bacula uses optional, high performance modules to extend its compatibilities and capabilities, but there are still ways to perform some backups (for example, SQL database backups) without these modules. One of these ways is where the backup process revolves around the simple process of dumping SQL databases to file and creating backups of these files via basic Bacula functionality.

You can learn more about the extent of Bacula’s capabilities regarding SQL database backups by reading this article.

PostgreSQL database backup

Another similar database type is PostgreSQL, and this one supports not only SQL querying, but JSON querying as well. PostreSQL might not be the most popular database type in the world, but it is nevertheless widely used and has a decently sized community that has supported it since its beginning.

There are several ways Bacula can be used for backing up PostgreSQL databases. One of these ways is like any other backup solution; Create a dump of the entire database and backup the resulting dump file.

PostgreSQL offers two ways of creating a backup file on disk:

  • Binary dump
  • SQL (text) dump

The main difference between the two is that a SQL dump is less complicated but consumes more resources during its creation. It generally takes more time to finish and uses more space on disk. PostgreSQL’s binary dump on the other hand is faster, less “needy” when it comes to resource utilization, and the resulting dump file uses less space on disk.

To learn more about the differences between these two, and to see how to perform these backups – please proceed to this article.

Conclusion

Bacula is especially versatile when it comes to handling different database types or different IT environments in general. It’s not hard to perform basic backup and restore operations, and you can always use the community’s help for more advanced processes, like scheduling. Bacula is a good choice for anyone with basic IT admin experience that is looking for a powerful solution that can reliably backup and recover a database, and would like to do it using free database backup software.

If you’re also interested in Server Backup, take a look at Bacula’s solution.