In a manner of speaking, planning and implementing a SQL Server backup design is an art. Backup, Restoration, Recovery, Business Continuity Plans (BCP), and Disaster Recovery (DR) are different phases of data revolving around the discussions involving data backup. In other words, it’s about how we ensure to retain the business data through any sort of situation that’s thrown at us.
Backup is probably the simplest and the most familiar process in most situations. A backup is a copy of the data derived from the production copy, stored at a location that’s different from where the production data is stored. A backup copy is used to recover data needed to restart an application correctly, after certain types of failures. Using various tools and techniques, the data is replicated to different geographies and is made available all the time. Some may even argue that having multiple working copies of the data somewhere else removes the need for having backups, but in a not-so-perfect world, backups are still required to make sure you can always recover from data mishaps.
What is a backup?
The word backup refers to copying and archiving the data so that it may be used to restore in case of an event of data loss. In general, you should back up any work or data that can’t be replaced easily.
A database backup is a copy of data from the database, which can be used to reconstruct the data. A database backup is a process to protect the data from various disasters. Disasters cannot be completely prevented; the least we can do is to ensure we have everything that’s needed to get up and running as soon as we can. Database backup is also performed and managed to ensure that an organization complies with business and government regulations. For instance, for legal reasons in the US, correspondences should be saved for at least seven years in the manufacturing industry. The process of backing up data is pivotal to a successful disaster recovery (DR) plan.
Organizations target for backup, the data they feel is vulnerable to:
- Corruption
- Failed hardware including media failure, software failure, and OS errors
- Attacks on the network or other malicious actions
- Manual errors or other inadvertent actions
- Unauthorized changes
- and other undesirable events
How often should I run my backups (frequency of backups)?
The frequency to run your backups depends on how often the data changes. You may find it helpful to consider the following points when planning your backup schedule:
- Business data that changes frequently may need daily or hourly backups
- Data that changes every few days could use a weekly or even monthly backup
- For some data, you might want to run a backup whenever you make a change
Predefined policies and Service Level Agreements (SLAs)govern the frequency and redundancy (or replicas) of the backup.
Backup destination
You can store backup files in one of the following locations:
- Local storage is used if you want to save the backup on a removable storage device attached to the computer or on a local computer drive.
- Shared folder is used if you want to save the backup in a network shared folder
- Tape devices are used in case robust archival is required.
- Cloud backup
- Public cloud storage: this type of storage is often used to backup the data as part of the Disaster Recovery where users send the data over to public cloud services such as S3. The providers generally charge based on monthly usage of storage. Some providers also charge for data transfers and access
- Private cloud storage: is referred to as internal cloud storage. In this case, the data is backed up to servers within the environment, based on pre-determined set of policies and company’s firewall
- Hybrid cloud storage: is a combination of public and private cloud.
Who should tailor your backup
The backup process is handled by Backup Operators. They are specific individuals given the responsibility to configure, schedule, initiate and monitor the backup process.
Understanding RPO and RTO
The RPO (Recovery-Point-Objective) and RTO (Recovery-Time-Objective) are vital parameters to consider when planning the backup strategy.
The recovery point objective, or RPO, is the point in time that data can be restored to. For instance, if the most recent available backup is a week old, then we’d be able to restore to what the database looked like at that time the backup was taken, so you will potentially lose a week’s worth of data. On the other hand, if your most recent backup was taken five minutes ago, then we can restore to that point in time and would lose only five minutes of data. So the question would be how much data is the organization willing to lose—data worth hours, minutes, seconds or nothing at all? If the plan is zero data loss, then backup alone is not the solution. We need to use additional tools and techniques to achieve the desired result.
The other term, recovery time objective, (or RTO) is the amount of time taken to perform the restoration. A very large database may take several hours or even a day to restore, which in some cases this may not be acceptable. Typically, running a restore takes slightly longer than running a backup, so if we do a backup which takes 30 minutes to run, it wouldn’t be surprising if the restore ran for longer than 30 minutes.
Summary
As a general rule, the amount of time in between backups should be no more than the amount of time you are willing to spend redoing any lost work. For example, if spending a week recreating the lost data is too long for you, you should back up the data at least once a week.
The additional techniques that we mentioned for zero-loss model is called High Availability in most situations. These may be very expensive for some organizations. On the other hand, backups are more economical to run. Therefore, because of our budget constraints, sometimes we have to live with the fact that we will lose a few minutes of data.
The recovery point objective and recovery time objective are metrics that are usually decided by the business. Several aspects of business are taken into consideration when coming up with these metrics. Once these objectives are decided, these must be clearly communicated to the stakeholders (IT and otherwise) within the organization.
Table of contents – SQL Server backup and restore article series
References
- Backup Overview (SQL Server)
- Restore and Recovery Overview (SQL Server)
- Overview of Restore Scenarios
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021