Backups are an indispensable component of all computing environments. Creating data backups regularly is considered best practice for everyone from home users who just browse the web and play games to DBAs responsible for mission-critical financial databases. As a database administrator, you never want to be in the position of explaining why you cannot recover your SQL Server that just got hit with ransomware. Let’s just say it would be an ugly conversation and perhaps one of the last ones you hold at that particular place of employment.
DBAs Have Some Decisions to Make
If you are a DBA, protecting the information in your systems should be your top priority. To accomplish that goal, you need a robust backup strategy. Whether you are implementing backups in a new environment or are reviewing a current plan to verify its reliability, there are some decisions you will need to make. These affect both the manner in which your SQL Servers are backed up and the way they can be recovered.
Recovery Models
A SQL Server recovery model is a database property that controls how transactions are logged, whether the transaction log can be backed up, and the type of restores that can be performed. Three different recovery models are available in SQL Server.
- Simple – The simple recovery model frees log space once a transaction is committed and data is written to a file. It enables self-management of log space but cannot be used for point-in-time recovery.
- Full – Using the full recovery model enables your team to perform point-in-time recovery. Space in the transaction log is not freed until the log file is backed up, meaning that backups are used to manage its size.
- Bulk-logged – This recovery method is used for less transaction logging and results in smaller transaction logs. It cannot be used for point-in-time recovery.
Once you have settled on the recovery method, you need to decide what types of backups will be used to protect your data.
Backup Types
Multiple types of backups can be used to protect your database. Often, more than one type is needed to support the level of coverage and recovery methods an organization requires.
- Full database backups are simple to perform and capture the complete database and part of the transaction log. This kind of backup can be very time-consuming and takes up a lot of storage space.
- Partial backups are exactly what they sound like. They back up a set of file groups and part of the transaction log for recovery. These backups will be smaller but read-only filegroups will need to be managed and backed up using another method.
- File group backups introduce another level of granularity and allow DBAs to backup specific file groups independently. File group backups are more easily scheduled and provide faster recovery speed but add administrative complexity to your backup procedures.
- Differential backups backup all changes to the database made since the last full backup. They run in a shorter timeframe than a full backup and can be used with any recovery method.
- Copy only backups do not reset the differential bitmap and will not break a defined backup sequence. This type of backup is perfect for making a copy of a production database to be used on a test machine.
- Transaction log backups protect active parts of the transaction log and allow for point-in-time recovery using the full or bulk-logged recovery models.
Designing the Backup Strategy
The next step in planning your SQL Server backup strategy is to understand the needs of the business. For each SQL Server instance, you should know the recovery point objective (RPO), recovery time objective (RTO), and maintenance window in which you can safely perform backups. The RPO is a measure of how much data can be lost and the RTO specifies how quickly the systems need to be restored.
In all but the smallest shops, you cannot have a specialized backup strategy for each SQL Server. A good way to organize your SQL Server assets is by tiers. For example, a three-tiered approach would put critical systems at tier 1, important production systems at tier 2, and test and development systems at tier 3. Then you can devise backup strategies that address the requirements of the tiers, leaving some room for some exceptions that may come up.
A common strategy involves performing full backups at regular intervals augmented by partial or differential backups to capture changes in important databases. Test machines should be backed up less frequently and their data may be retained for less time. The overriding goal is to be able to meet the RTOs and RPOs for each system when the need arises.
Implementing the Backup Strategy
The backup strategy put in place for your SQL Servers needs to work together with the overall enterprise infrastructure backup plans. You need to take into account how your database backup windows affect other parts of the computing environment. Implementing compression can help minimize the impact by reducing the size of the backup files.
I would like to draw your attention to a very informative webcast that takes a deeper look into designing the right SQL Server backup strategy. Viewing the webcast is time well-spent for DBAs who are concerned with fully protecting the data resources for which they are responsible.
Along with the insights provided in the webcast, another factor to be considered is how you plan to manage your backups. IDERA’s SQL Safe Backup offers a platform that provides advanced backup and compression functionality as well as the ability to manage all your environment’s backups from a single interface. You can catch a demo of it at the end of the webcast. It’s a great way to ensure that you never have to explain why the databases are not backed up or recoverable.