While making a database backup is not a difficult task, making one the correct way can be a little more involved. So if you’re new to the process, setting up and maintaining backups on multiple servers can initially seem intimidating.
There is no one-size-fits-all solution. Choosing a good solution depends on your individual architecture, resources and security requirements. In this article, we will discuss the main approaches to managing multiple database backups, and we’ll look at the pros and cons of each approach.
Custom solution
The first thing that comes to mind is to create a good backup script and place it “next to” the database.
Such a script should include the following steps at the very least:
- Backup creation
- Backup compression
- Backup transfer to the repository
- Removal of old backups
- Backup job notifications
Below are some articles with scripts to help automate backups on the server:
How to Automate MySQL Database Backups in Windows
How to Backup and Restore PostgreSQL Database on Windows
How to Backup SQL Server Databases on Windows
How to Automate MySQL Database Backups in Linux
How to Automate PostgreSQL Database Backups in Linux
How to Backup SQL Server on Linux
Essentially, the above solutions are for a single server. For numerous servers, sending backup job notifications is not enough as they can easily get lost among other emails. Additionally, you need to have information sent to the dashboard.
Pros:
- Flexibility. You can create a solution that is perfect for you.
Cons:
- It takes time. Development and debugging of a good solution requires a lot of time. With a large number of servers, it is desirable to create a database that will accumulate and display data on the dashboard.
- Time-consuming maintenance. It’s not enough to just set up backups and forget about them. With a large number of servers, a custom solution will require maintenance. If something goes wrong, you will have to fix it. You also need to test backups from time to time.
- Risk of confusion. In backup storage, files should be stored so that it is possible to discern them unambiguously. When configuring backups on each individual server, there is a risk of copying settings from another server. In addition, the job gets even more complicated if your architecture allows the same names for servers or databases.
Third-party solutions
In large companies, data protection is handled by DevOps. But there is also a different way. You can delegate the creation of database backups to another company that does backups as a service.
Backup as a service (BaaS). It is a backup concept in which the backup is done by an IT infrastructure provider or a third-party company.
Let’s look at the types of such a service, how they differ, and what the pros and cons are for each.
Agent-Based Backup
In order for a third-party company to make a backup, it must have access to the database. It is bad practice to allow access to the database from the internet. Therefore, the client installs an application (backup agent) on their server, in which they set up access to the database. Now a backup agent can receive commands to make backups.
However, making a backup is not enough. The United States Computer Emergency Readiness Team recommends the 3-2-1 rule:
3 – Keep 3 copies of any important file: 1 primary and 2 backups
2 – Keep the files on 2 different media types to protect against different types of hazards
1 – Store 1 copy offsite (e.g., outside your home or business facility)
After the backup of the database is made, a copy of it must be taken out of the IT infrastructure in which the database is located. The backup agent does this too. Furthermore, as a rule, the backup agent compresses the backup, encrypts it, and restores it in case of failure.
A great place to store a backup file is public cloud storage such as AWS S3, Azure Storage, Google Drive, Dropbox or OneDrive.
The SqlBak service specializes in backing up databases and transferring them to one or more cloud storages.
All you need to do is to install a tiny SqlBak App and set a connection to your DBMS.
Once the connection is set, you can create a backup job to backup your databases.
With SqlBak you can manage all the connected servers from one dashboard page.
As a rule, services like SqlBak can do not only full backups, but also incremental ones. Incremental backups are very small in size, which allows them to be made frequently and stored for a long time.
Pros:
- Flexibility. Backup frequency, storage rules and terms are determined by you, depending on your requirements.
- Any cloud storage. You can choose the most suitable cloud storage (considering cost as well).
- Dashboard. Information updates about all servers, databases, and backups are on a single dashboard.
Cons:
- More invoices. Two different payments: one to the cloud storage, the other to the backup service.
- Dilution of responsibility. Backups are made by one company and stored by another.
Backup service by hosting
When buying any server in a data center, you can additionally pay for a server backup. To create a backup, the hosting provider creates a snapshot of the entire file system. Since the database is on disk, it gets into the backup and can be restored.
You cannot just copy the database files. However, all popular databases have mechanisms for reconciling data when creating a disk snapshot. For example, Windows Shadow Copy on Windows allows you to copy SQL Server files securely. However, this service must be enabled.
Before trusting the backups of your hosting provider, make sure that the DBMS configuration you are using can be restored to a consistent state from a snapshot.
As a rule, the hosting providers store backups at their facility and do not provide direct access to the backup file. However, they can restore data from the backup to the server in its data center on demand.
For example, Hivelocity makes backups of all hard drives specified by the client once a day. Backups are stored for 7 days and can be restored at the request of the client.
Clients receive a daily report regarding the created backups.
Because of the considerable size of the backup, you cannot make a lot of them. Therefore, for all hosting providers, the frequency of storing backups and the duration of their storage leaves much to be desired.
Pros:
- Backup and restoration of everything. If the server fails, you can restore the entire system at once without any additional configuration.
Cons:
- Full recovery only. You can only restore the entire system — you cannot only roll back the database to an earlier date.
- Limited number of backups. You cannot choose the time and frequency of backups.
- Putting all your eggs in one basket is bad. Both your server and your backup are hosted by your hosting provider.
Cloud Database Backups (Database as a Service)
A cloud database is a database that is built and accessed through a cloud platform. The user gets TCP/IP access to the database and can manipulate the data. It is not possible to access the computer that physically stores the database.
The advantage of such databases is not only in limitless scaling but also in reduced administration costs.
Backups in cloud databases are made automatically in point-in-time-recovery mode, while the backup files themselves are not available for download. Amazon Relation Database and Azure SQL do not charge extra for regular backups, but the backup frequency and retention period are limited. However, for an additional fee, you can store database backups for as long as you need.
For example, in Azure SQL, backups are configured at the server level. You can set rules to automatically create long-term backups.
You cannot set rules for creating long-term backups in AWS RDS, but you can take snapshots of the database directly from the web interface. Here you can read more details about how to backup Amazon RDS SQL Server.
Pros:
- All-inclusive. Automatic backups for every database are included by default.
- Point-in-time-recovery. You can restore data to any point in time in the last 7 days.
Cons:
- Moving. Moving to the cloud just for the sake of backups is too difficult.
- You cannot get the backup file. The database can only be restored from the same cloud provider.
- Limited number of backups. Long-term storage of backups is either limited or requires manual management.
Bottom line
Undoubtedly, the best solution for backing up databases on multiple servers is to hire an employee, or IT department, who will write and maintain their own backup system. However, due to the high price, this can prove to be unattainable. In conditions of limited resources, delegating the creation of backups to another company is a good decision. If you plan to move to the cloud, the built-in backups are an additional argument in favor of the move, but you shouldn’t move to the cloud just for the sake of backups. Dedicated backup creation services like SqlBak allow you to centralize the management of backups in one place and restore data in one click. However, such services are not free. The backup that the hosting provider makes will allow you to restore the entire server along with the database, but the number of recovery points will be small.