A quick overview of database backups

Backups are one of the most important things to get right.  You need backups in case your server blows up or someone drops a database while drunk at a Christmas party, but first you need a backup strategy or schedule. To formulate this, consider the following:

• Database size - how long will backups take
• Office hours  - what times the database is in use
• Average rate of change to database (per hour) during office hours
• Recovery Time Objectives (RTO) – ie the database must not be out of operation for more than 2 hours
• Recovery Point Objectives (RPO) – ie when database is recovered, no more than 3mins of transactions may be lost

You can create backups on the fly, but you'll want to automate their creation using Jobs.

There are 3 types of backup to consider. All 3 should be done in a combination best suited to the business. You can back them all up to a backup device. Click the links below to see instructions about backing up to the backup device 'AW.bak' :

1. Full backup – the data files and the active part of the transaction log
2. Differential backup – the data from the time you made your last full backup
3. Transaction log backup – records all transactions.

It is worth remembering that a backup represents the state the database was when the backup finished not began, which is good.

System databases must be backed up also, and you should seriously consider offsite backups.



Further reading: Jobs, System database backups, Tail log backups, Media sets, File/filegroup backups, Compression