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.
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 a zero-loss model are 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 the 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.