Databases store the most valuable information for the businesses you work with and they’re the trickiest systems to back up; some of the largest technology companies in the world have got database backup badly wrong. Losing that data can have serious consequences for the business, to say nothing of the embarrassment quotient for you. Here we explain the essentials of backing up and introduce some specialist tools.
Happily there is a whole variety of ways in which a database can be backed up, and the biggest divide is between offline and online backup.
Offline or file backup
An offline backup is backup at its simplest. Given a database that’s operational between 9am and 6pm, at 6.02pm you take a copy of the database. The advantage of this is that it is straightforward and highly applicable to databases held, for example, in Microsoft Access, where you can just copy the .ACCDB file (or .MDB for versions earlier than Access 2007) to a remote disk or other medium. With a small database you can do this every night: using Access 2007’s backup option, the backup is automatically named and dated (MyDatabase_2009-11-05.ACCDB).
The disadvantage is that if the customer’s database crashes during the day, they’re in danger of losing all the transactions since the previous night’s backup. It is, of course, possible to stop all work on the database and make a backup at lunchtime or even every hour; whether the disruption is worth the reduction in potential data loss is a trade-off that you and your customer need to discuss.
And here is one important decision point; if your customer can’t afford to lose any data, use a relational database management system (RDBMS) that’s more robust than Access and which offers logging and online backup.
Enterprise-level RDBMSs will store each transaction in the database and also in log files. These can be used with offline backups to help protect a database in a number of ways. Imagine a database that was backed up offline overnight; users start work at 9am and at midday the smoke gets out of the database server. With the backup and the log files, you can replay the log files against the backup to bring the database back to the state it was in just prior to the crash.
Online backups are more complex than offline ones. A large, busy database can undertake thousands of transactions during the several hours it might take to back up. Some transactions may be incomplete when the backup is generated and the resulting data set will be in an inconsistent state. However if the backup software ‘understands’ log files and can identify those for the period when the backup was being made, the backup and logs can be used together to restore the database and produce a completely consistent backup.
A further level of protection is offered by an Image backup, also sometimes described as a ‘bare metal restore’. This is a copy of the whole machine: the operating system, applications, data, drivers, patches – the works. It’s especially useful if a server fails catastrophically. If you have only a database backup, it can take hours, even days, of work to bring a replacement machine to the state where you can restore the database. An image backup lets you automate the rebuilding of the machine in its entirety.
Built-in backup utilities
The natural progression for many Access databases is to Microsoft’s SQL Server which has built-in backup. The difficulty here is that the backup features you want may figure only in the more expensive versions. Only the top-of-the-range Enterprise edition supports the full set, including log shipping (moving the log files to a copy of the database on another machine), backup compression and database snapshots. SQL Server Express edition (a free download) provides a popular upgrade path from Access but offers none of these features (although you can perform a simple backup from SQL Server Management Studio Express).
Instead of specifying more expensive versions of the database, use third-party backup utilities which have the extra benefit of having friendlier user interface than some of the built-in tools. Most are available as time-limited trial downloads of between 14-60 days to try out.
Redgate’s SQL Backup v.6.2 (red-gate.com/products/SQL_Backup) is backup software for SQL Server 2008, 2005 and 2000 databases. Available in Pro and Lite editions, it supports many useful operations such as backup compression which shrinks the file sizes to reduce the storage space required. Pro has four compression levels (plus level 0, which is no compression) and Lite only offers the least intensively compressed level. You set the required level during the backup definition process, and if you are not sure which level to use, a Compression Analyzer will determine the best choice for the size of your customer’s database.
You can make a full backup or a differential one (which records changes made since the last full backup): given a full and a differential backup you can restore a database to the point at which the differential backup was made, and if you have transaction log files as well, you can restore to any point in time. You can also choose to back up just the transaction log files. Other options are to split the backup between several files (which can speed up backing up) or to mirror it to another location. Files are given default names but these may be changed if required, and files can be copied to a specified network location. Old backup files can be purged either by overwriting or deletion depending on their age or quantity.
SQL Backup offers network resilience by managing the transfer of backup data to a network location even if there are network outages: it automatically resumes data transfer once the network is again operational. You can change the re-try settings to suit the behaviour of your customer’s network. Network resilience is new to version 6 and available only in the Pro edition.
Before starting the backup operation you can review a summary of the settings and see the SQL script generated automatically by SQL Backup. Once the job has completed, the software reports whether it succeeded or failed and the steps are recorded in a ‘time line’. The time line also lets you review past backups and restores and the feature is supported by both editions. Pro also offers 256-bit data encryption, support for 64-bit versions of SQL Server and email notifications.
Prices for the Lite version start around £200, Pro costs from around £500.
LiteSpeed for SQL Server 5.1.1 from Quest Software (quest.com/sql-server/backup-and-recovery.aspx); an Engine for Oracle version is also available. It has a Backup Analyzer which automatically runs tests and generates a customised recommendation. Ten levels of compression are supported, it too protects with 256-bit encryption and shows a time line view of backup activity. SmartDiff is Quest’s new technology for specifying the conditions under which a full or differential backup should be instigated. This wizard-operated tool lets you choose options such as scheduling, notification and backup verification.
Quest has a range of editions SQLBackupAndFTP is backup software for SQL Server Express (and SQL Server): it will run scheduled backups and zip and encrypt them, storing the backups elsewhere on a network and will even send out an email reporting success or failure of the operation. The basic version is free and lets you work with a couple of databases; the premium version is $49 for unlimited use. There’s also SQL Server Backup 7.8.8 costing between $99 and $169.
Yosemite Server Backup Plus from BarracudaWare (barracudaware.com/products/server-backup) is another option; it supports up to three servers and comes with an advanced agent for SQL Server to let you make online backups. It supports encryption and compression and has a Bare Metal Restore feature, which lets you restore a complete system from backups without a separate image backup. Prices start from around £550. Pick the utility that matches the customer databases you need to protect and the backup systems you’re already using to protect their servers – and test the restore process as often as you do with other backup tools.
SQL Backup demonstration videos
RedGate has a range of videos which provide good walk throughs of how to use the product:
Best Practices for SQL Server backup maintenance
Two years old but this guide is still appropriate for many SQL Server sites:
All About Backup
As the name suggests, this a generic site about backup techniques with some useful references: