The special case of database backups

Database requirements
The ever-increasing dependency of many enterprises on IT entails an enormous risk – the total loss of a database would cost some companies a great deal of money and, in the worst-case scenario with the collapse of the foundation of their business, would lead them into bankruptcy. It must not be forgotten that there are also legal requirements which demand the long-time archiving of digital data in companies. These databases must therefore possess the highest flexibility, and faultlessly supply data at all times while tolerating only the minimum downtime. It comes as no surprise, then, that IT managers look for solutions which first of all ensure the stable, fail-safe operation of the database, and secondly allow complete backup of the database, and a recovery scenario without data loss.

To understand the possible problems and repercussions of an incorrect or incomplete data backup, we must first deal with the functionality and operation of the databases used. Generally, in addition to the actual database, a transaction log is generated, in which all transactions and database changes are recorded. Both components must therefore be backed up, if possible at regular intervals, and always with 100% synchronisation.

Incorrect backup strategies
Databases are essentially characterised by dynamism. New data is constantly being written, and existing database entries modified or deleted. It is therefore not appropriate in practice to regularly back up the database to tape or hard disk overnight or at the weekend. Databases do not fit into the context of a conventional backup strategy. For example, if the database must be shut down during the backup (cold backup), it is not available in the production environment (downtime). In an online shop, which is open 24 hours a day, no orders can be placed during that downtime.

Much more dramatic than that, however, is the fact that in the event of recovering an old database that was backed up several hours before the crash, it's inevitable that valuable information added since the last backup will be lost. In certain circumstances, this could impact the entire logistics of a company’s operations. For example, if orders and deliveries vanish from the system, then the goods receiving and goods issuing departments are working with incorrect figures. In the best case, the missing data can be added to the transaction log manually. Although this is labour intensive, it is more the exception than the rule.
Special data backup tools work better, but are not ideal. With the aid of so-called open file backup options, these tools wait for an access-free time before backing up a more or less 1:1 image (snapshot) of the opened file to the server’s hard disk. However, caution is advised. Only if the data is buffered during the backup and not written to the transaction log does the database remain consistent.

Recovery with tricks
Even if a complete or differential backup of the database and transaction logs exists, a recovery can still be plagued with problems and can fail. In Microsoft SQL Server, for example, all existing transaction logs (created after the last complete or differential database backup) must be recovered in chronological order. If a transaction log in this log chain becomes lost or damaged, only the transaction logs before the missing one can be recovered.
A step forward is the Point in Time Recovery procedure using the Write-Ahead Logging (WAL) technology. All transactions are saved to log files from a consistent status (the so-called checkpoint). In this way, it is possible to restore a database to a consistent status after a crash. When restarting the database server, it starts from the last checkpoint, reads the transaction logs and executes all the transactions logged in them. In this way, the system is restored to the status it had before the crash.

Security is not a question of budget
The operation of primary and secondary servers is secure, though expensive and technically complex to implement. In the event of a system failure in the database server, in a fraction of a second it switches to the standby system which always possesses an identical database. Many companies recoil from such a solution because, apart from the duplicated hardware requirements, they are also lumbered with double licence fees for the operating system and database system if open-source solutions are not used.

As a further development of Point in Time Recovery, the technology known as Automatic Recovery to Point of Failure aims to become established as the modern and reliable backup solution for databases. It intends to make the same high security and data availability possible that can otherwise only be offered by hardware solutions with primary and secondary systems. 

The basis of Automatic Recovery to Point of Failure is the complete and differential backup of databases and their associated transaction logs on the fly – automatically and without the manual intervention of the administrator. The highlight? In contrast to previous solution approaches, in the event of a disaster the data is not recovered to a fixed backup time, but to the state immediately before the error occurred. In recovering a database backup, the transaction log of the database is read. Only in the rarest of cases is this log also affected by the crash. Using this log file, the last actions of the database can be duplicated completely, automatically, and the database can be restored as close as possible to the moment before the crash. The automatic functions remove the need for the labour-intensive manual tracking of log files.

Acronis Recovery for MS SQL Server is one of the first products to combine the full recovery of a database to a freely selected time (Restore to Point in Time) with automated recovery to a point in time immediately preceding a crash (Automatic Restore to Point of Failure). This means that critical, database-driven business functions can be quickly restored in the event of a crash, with minimal disruption to business operations. Superior database recovery certainly saves time, and could potentially save the company.