Types of Database Backup (and Frequency)

This is a continuation of Monday's post about Backup Basics.

There are different types of backup

Think for a second about how databases work. They typically have a large pool of data to deal with that doesn’t change that often (like all of your resolved Incidents) and then smaller amounts of new data and data amendments to record, which it needs to store as quickly as possible. The way that such systems handle this is by having a Transaction Log, which is where all the changes go.

Think for a second about that this means. It means you could take a backup of the data that does not change a lot, and then just keep backing-up the Transaction Log so that you are backing up all of the changes you’ve made.

So, SQL Server has two types of backup. There’s the Full or Complete backup, and the [transaction] Log backup. One backs up everything, and one backs up data changes.

So why bother? The answer lies in frequency of backup. Full backups can slow down your system, simply because the database has a lot of work to do is gathering and then writing all of your data. So, these are typically done just once, when the database is quiet (like late in the evening). The rest of the time, you perform Log backups to record your changes because this is much quicker, and places less of a burden on the system.

Tip: Don’t perform multiple Full backups during the day – your users will notice, particularly if you have a large amount of data.

If the worst happens, you restore from your last good Full backup, and then perform rollforwards using the backups you’ve taken of the Log – effectively re-applying all the changes you made.

When you perform your SQL Server backup, one of the things you can choose to do is to truncate the Transaction Log (otherwise, on SQLServer, it will just keep on getting bigger).

Frequency of Backup is Important

If you do your backup once a day, think about how many hours of data you could lose if it all goes pear shaped – almost a day’s worth. As a manager, think about your Helpdesk or Service Desk’s throughput during the day and how much data you could ‘afford’ to lose, and then build your backup strategy around this, for example by having a Full backup during the day, and an hourly Log backup during the day. Remember, don't rely on fault-tolerant hardware. If there is a fire, theft, or one of the engineers spills his bottle of Irn Bru over the server, you could lose everything in an instant.

Using 3rd-party software

Using third party utilities and software to backup your database is fine, provided you choose wisely. However, don’t think that will necessarily excuse you from understanding the issues I’ve highlighted above. All such programs that I’ve seen simply provide convenient interfaces to SQLServer’s own backup mechanisms – so everything I’ve said above still applies. You still have to think about frequency, type of backup and log truncation.

Practice, Practice, Practise

Have you ever practised recovery? Don’t make your first recovery a real recovery – practice will prove that your backup and recovery procedures are effective. Don't trust to luck.

A Word of Caution to Managers

Users tend to hold senior managers responsible for data loss. Take an interest in your backup and recovery procedures, and don’t assume it’s all been taken care of.