For almost any DBA, the question “How often should I backup my database?” is undoubtedly one of the most important to ask and answer. The answer, as with so many other questions in life, is “It depends”. Depends on what? Let’s discuss this.

Factors affecting database backup frequency

The first and most important question to ask is how critical or sensitive your data is. This is also usually also closely tied to another question – How quickly do you need to recover your data? For some organizations, the databases and the applications running on top of them are their lifeblood. Without them, their operations grind to a halt within minutes, for example, the databases that manage the transactional systems for commercial banks. Other systems are so critical that even the government monitors and requires certain safeguards for the databases that rely on them – think commercial airports and nuclear power plants. For these and other types of super-critical databases, very frequent backups several times a day are expected. But even backups alone may not be good enough – they may also make use of database replication, which is essentially a real-time backup.

But for many other databases, you may not require this level of intensity. For instance, if you have an application in which you enter only a few dozen transactions a day and you can do without access to the system for a day or two, a full daily backup may suffice.

Other important points to consider are what type of data is contained in the database. Even for a bank, its different databases are not equally critical in terms of recovery time and availability. The database handling the transactional accounts is likely to be more critical than the one handling long-term loans and mortgages if these are on different databases.

The main questions to ask yourself while designing a backup strategy are outlined below, and also discussed at length in this article on the MSDN site:

• How important is the data on your systems?
• What type of information does the data contain?
• How often does the data change?
• How quickly do you need to recover the data?
• Do you have the equipment to perform backups?
• Who will be responsible for the backup and recovery plan?
• What is the best time to schedule backups?
• Do you need to store backups off-site?

Types of Backups

The types of backups you require are also largely informed by the same questions above. The mix-and-match between full backups, differential/incremental backups, and transaction log backups depends a lot on your backup strategy.

Remember that the backup process itself introduces some locks on your database and consumes resources. So keep in mind how busy your database is and its size; these are the 2 main factors you need to carefully consider while scheduling your backup frequency.

Sample Predefined Schedules

We’ve already mentioned this before, but it bears repeating because it is that important. Several factors dictate your backup schedule. Two of the most important are your data’s criticality, and how busy the database is, which can be thought of as the number of transactions per hour/day. Consider these predefined sample schedules as examples:

Lazy – Full backup every 168 hours (weekly), Differential every 24 hours: This is ideal for a database with just a few transactions per week or one whose importance is not mission-critical. The loss of a few hours of data can be tolerated or easily recreated. A real-life example would be an HR/ payroll system for a very small company – very infrequent changes in personnel changes and easy ‘recreatibility’ results in little need for frequent backups.

Daily simple – Full backup every 24 hours: In this type of backup, you simply take a daily full database backup. This is usable in situations where transactions are more frequent than ‘Lazy’ above, but transactions are still recreatable from manual records or other sources. Example – an accounting/ payables system for a small company that issues and keeps copies of manual receipts.

Daily 4 times – Full backup every 24 hours, Differential every 6 hours: this is suitable for databases that incorporate several more changes during a typical working day, and are thus much busier than ‘Daily simple’ databases. Such as an accounting system for a midsize company or a bank’s mortgage-processing system’s database.

Thorough – Full backup every 24 hours, Differential every 4 hours, Transaction log every 1 hour: now we are into the realms of databases whose loss can be considered catastrophic or at the very least serious hindrances to business operations. Such databases may host applications writing several dozen or hundred transactions per hour, so even waiting the 4 or so hours between differential backups means a potential loss of a significant chunk of the day’s transactions. Additionally, there is likely no manual recording of transactions, so trying to recreate lost transactions would be a major pain – hence the need for transaction log backups. Examples are large insurance-company databases and midsized online retailers.

Maniac – Full backup every 24 hours, Differential every 3 hours, Transaction log every 15 minutes: for very busy mission-critical database systems. These are systems that simply cannot afford to drop any transactions. Due to the load placed by the frequent backup jobs, these are also likely to offload the backup job to a secondary server. Think commercial banks’ transactional systems, large hospital admissions systems, large online retailers.

How to schedule backups in SqlBak

Setting up a backup schedule in SqlBak is easy. After determining your databases’ backup frequency, you set it up and configure it at the Schedule Backup Frequency step in SqlBak. Expand Show advanced backup schedule, and then select and customize your options for full, differential, and transaction log backups. For example, your database is very busy throughout the day, and you determine that you need to take a full backup daily, a differential backup every hour, and transaction log backups every 10 minutes. Your setup in SqlBak would be as illustrated below.

Summary

For a DBA, the choice and frequency of database backups is critical and fundamental part of the job. And this should not be viewed as a one-off exercise either. As databases and the applications they support change in importance due to business needs, and new ones are added, and others retired, this decision should be reviewed and revisited as often as necessary. Several factors are affecting this important decision, and from this post, you should have a good idea of some of the most important ones to take into consideration.