Automatic DB Backup Strategies. Cold and Hot Backup to USB, NAS, FTP, Online

Comments: 0

Database Backup Features

Automatic backup plan for databases is a vital part of any successful disaster recovery strategy. Databases are used everywhere, from schools and governmental structures to Fortune 500 enterprises, and their failure can cause millions of dollars in loss. That is why it is so important for backups to be consistent, error-free, and recoverable in a timely manner.

While the concept of database backup is simple, it is often not easy to find software that can provide a reasonable level of safety. We recommend you to try out Handy Backup™ – a professional utility with a long track record of resolving problems of customers from very different industries. Our software supports MySQL, MS SQL, Oracle, DB2 and many other databases (see the list below), and has all the features that you may be looking out for, including scheduling, encryption, incremental/differential backups, and other.

Version 7.13.0, built on 26 May, 2018
162 MB

This page describes features that will help plan your backups in an effective and reliable way. We also highly recommend reading general information about database backup and its types. To do it, please see Database Backup.

Contents [hide]

Best Practices and Strategies

Efficiency of a backup plan is defined by the time it takes to roll back to a recovery point after an error and the amount of efforts and resources required for all operations. When implementing a DB backup strategy, it is important to take into account two things:

  • Database granularity and desired speed. Often, limiting backups to only the necessary and sufficient information is not worth it, because working on such a deep level may be inappropriately slow. A good practice is to make table-level backups, and combine it with incremental/differential methods. Note, however, that some DBMS have limited APIs and allow backing up complete databases only.
  • Storage options. A good backup solution must do everything on its own, while requiring the minimum amount of attention. This means that there must be flexible scheduling capabilities, built-in compression and encryption, incremental and differential backup methods, etc. (storage options are discussed below).

Handy Backup has six plug-ins designed for databases of different types (you can learn about each plug-in on its individual page):

Although most of the supported databases are ODBC-compatible, we highly recommend choosing database-specific plug-ins over the general one (e.g. MySQL Backup over ODBC). Please see backup recommendations for different databases below.

Recommended Solution

Handy Backup Small Server

Version 7.13.0, built on 26 May, 2018. 162 MB
Backup Software from Novosoft LLC. 249 USD per license.

Handy Backup Small Server

All database backup features are included into the small business server backup edition of our software. Full-featured 30 days trial version!

Physical Backup (for MS SQL Server, MS Exchange Server and Lotus Notes)

Physical backup is the fastest and one of the most reliable methods of backup. It is based on working directly with the DBMS binary files, rather than accessing its data on logical level. Basically, it works as follows:

  1. The application instructs the DBMS to flush all open transactions and cached data to the HDD.
  2. Then, the application makes a snapshot of the database files and copies them to a safe place.
  3. When the copying is finished, the snapshot is destroyed, and the DBMS continues working in a normal way.
  4. In the end, some DBMS can also inform the application of the changes made during the copying process, so it can update the backup to the most consistent state. For example, this is how the Lotus Notes Backup plug-in works in Handy Backup.

Creating snapshots of database files is not a trivial matter, because many DBMS work with their files exclusively and prevent any other software from reading them. Our utility uses the Volume Shadow Copy Service (VSS) provided by Windows OS, which lets it backup Microsoft Exchange Server and Microsoft SQL Server through the backup-related API provided by these applications.

Since VSS is Microsoft’s proprietary technology, most databases don’t support it (the only exception is Oracle, which is discussed later). The good news is that there is a workaround that lets you back up VSS-unaware databases without stopping it. The idea is as follows:

  1. Begin a new transaction, so the DBMS locks the database for writing new data. This is an easy operation, because Handy Backup allows running custom scripts before and after backup.
  2. Back up DBMS files. The VSS-based Open File Backup feature of our software will be used automatically.
  3. When a backup is made, commit the transaction.

If your database doesn’t have a transaction-safe storage engine, you can simply copy it using the same Open File Backup feature. To learn more, please see VSS Backup...

Version 7.13.0, built on 26 May, 2018
162 MB

Logical Backup (for MySQL, Oracle, DB2 and ODBC Databases)

Logical backup is a method that accesses data through functions and interfaces provided by the DBMS. It is slower than files-based backup, but allows copying objects without knowing their locations, even if they occupy a small part of a file, or if they are split across several files or servers.

When using logical access to DB objects, the program communicates with the DBMS as a common client application. On the one hand it is easier and safer, because client apps never see inconsistent databases. But on the other hand, for large databases (of 10GB and more) logical backup procedures become really slow and resource-heavy. It’s the matter of concept – all internal optimizations, memory management, indexing, and other DBMS features don’t help.

As a general rule, it is recommended to choose physical backup over logical backup. However, most DBMS provide no choice, and that’s why logical approach is implemented in MySQL, DB2 and ODBC plug-ins of Handy Backup.

Important note on Oracle DB: While Oracle is VSS-aware and comes with a VSS writer that allows making physical backup, the Oracle Backup plug-in uses logical approach. If you are interested in backing up Oracle using VSS (in Windows), please vote for Oracle Backup using VSS in the Coming Soon section.

Handy Backup Small Server
Handy Backup Server Network

Two editions, one setup file: Small Server and Server Network - try now!

Cold Backup and Database Replication

Cold backup is the simplest backup to implement. It assumes that before making a copy, you stop the database, so it doesn’t get any updates while being backed up. This technique gives no regard to DB type: “cold” files are simply copied with common file system queries, in the fastest way possible.

While this method may seem too costly, as it produces server downtime, there are schemes where can work very well. Particularly, it can be combined with database replication.

Replication is a configuration of two or several databases in which one serves as a Master, and the others serve as read-only Slaves. Synchronization of data between servers is performed in one way (Master to Slaves) and doesn’t require permanent connection, i.e. can be done asynchronously. Basically, that means that you can safely stop any Slave server, make a backup, and then run it again, with no harm to the entire system.

Besides database backup capabilities, it is important for the software to support different storage media, processing options, scheduling and incremental/differential backups. You can learn about it below, or download our software without further hesitation.

Storage Considerations

The very idea of backup suggests copying important data to a safe storage, so it can be restored in case of a data loss incident. There are three main aspects of storage that must be taken into account:

  • Storage media. Different media provide different levels of security. For example, backing up to USB is fast, but requires you to have a fire- and water-proof safe to store the backup disk (if you want real protection). Online backup lets you keep data offsite and protect it from “local” risks, but relies on Internet connection and is not that fast. Finding storage that works best for you may take some time, so it is recommended that you have as many options as possible.
  • Compression. Storing backups in an uncompressed format is basically a waste of money. But though the idea of compression is simple, it’s not as easy as it seems. For large amounts of data (and database usually occupy a lot), it is important to create ZIP backups “on-the-fly”, without temporary files. You don’t want to see your server down just because temporary files ate away the whole hard drive, do you?
  • Encryption. No matter where you store archives, locally or remotely, make sure that they are protected from third-party access. Lack of backup encryption is one of the biggest sources of data leakage in commercial environments. And similarly to compression, it is important that encryption is made without filling the HDD with unnecessary temp files.

One of the most powerful sides of Handy Backup is that operations are based on data streams and are performed as resource-friendly as possible.

Data stream path during database backup

Database backup: data stream path

In addition to local, external and NAS drives, our program enables you to back up to FTP/SFTP/FTPS and WebDAV servers, Amazon S3, and make use of the optical media (CD/DVD/Blu-ray).

Incremental/Differential Backup and Scheduling

There are techniques that allow minimizing disk storage required for regular backups. All of them suggest that you start with a full backup, and then copy only changes, saving on data already backed up:

  • Incremental backup indexes all backups and compares them to a current state, which allows identifying and copying only new and modified files. To roll back to a recovery point, you need to have the full backup and a complete chain of all increments. Learn more about incremental backup...
  • Differential backup works similarly to incremental backup, but computes differences with the full backup, instead of previous backup. Read more about differential backup...

In Handy Backup, incremental backup is made on file level (if the file changed, it is copied completely), and differential backup is made on byte level, i.e. it computes the smallest changes in backups. You can schedule your backups to take place each day, week, day of months, or specify any custom period of time.

Video Example

Below you can see a video example, where MySQL 5.5.16 is backed up with the MySQL Backup plug-in of Handy Backup 7.3.0:

We welcome you download our software and start your free trial period today!

See Also

Try Handy Backup - the most cost-effective backup solution for any database!

Version 7.13.0, built on 26 May, 2018
162 MB

Loading comments, please wait...


    Contact Us

    To back up databases, you need the Small Server edition of our utility:

    Handy Backup Small Server

    $249 per license

    Upgrade to version 7

    Volume Discounts
    1 license$249 per unit
    2-9 licensescontact us$249 per unit
    10-49 licensescontact us$249 per unit

    See all volume order prices

    Related to Database Backup

    SQL Server 2014 Backup Database - learn how to back up Microsoft SQL server in the most effective way

    Hot Backup - learn about “hot” backup of different data, performed in both physical and logical ways

    Jira Backup Database - a variety of possibilities for Jira automatic backup

    Navicat Backup Database - a tool for administering SQL databases (including MySQL, MariaDB, Oracle, etc.) vs our software

    Backup Firebird Database - a perfect solution for Firebird backup and recovery

    Who uses our backup software?