By: Jeremy Kadlec | Comments | Related: > Backup
Problem
As a best practice we issue full SQL Server database, differential and transaction log backups. We have setup a process to backup to local disks and then also copy the files to a centralized set of storage. On a weekly basis the centralized file system is backed up to a tape backup device. The tapes are rotated on a weekly basis in order to maintain 4 weeks of data on the tapes stored off site. I know I can get data off of the tapes, but that process is time consuming, not well tested from my perspective and I am not in control of the overall process. Can you offer some recommendations from a SQL Server backup retention perspective?
Solution
SQL Server backups are absolutely critical for disaster recovery, point in time recovery and researching critical data issues. Without the backups the opportunity to recover is impossible, but backups are worthless if they cannot be restored. In many respects issuing database backups can be considered mundane, but the recovery process at times can be intense if you run into issues while trying to answer a question from an executive three pay grades above you. As such, let's take a look at the SQL Server backup retention situation from these perspectives:
- Legal Requirements
- Recovery Reasons
- General Best Practices
Legal Requirements
In some industries three or seven years of backups are required for legal\compliance purposes. If you work in a health care, legal, financial, government, etc. organization then many of these requirements are established for you. As such, familiarize yourself with the requirements and be sure you are meeting the minimum requirements. If the minimum requirements, do do not meet best practices, you have the opportunity to show your team how you are exceeding the requirements providing a higher level of service.
The big item to keep in mind over the three or seven year period are all of the components necessary to recover the data. Think about the hardware (tapes, tape libraries, tape drives, available storage, etc.), software (Windows, backup software, SQL Server, service packs, etc.), passwords, etc. needed for recovery. This means that you need to maintain an environment to support the recovery or be able to setup an environment for recovery purposes.
Recovery Reasons
If you do not have legal\compliance requirements, then take a good hard look at your business to determine what you would need to recover from under a reasonable set of circumstances. Some of these items may include:
- Site failure
- Server failure
- Database failure
- Database corruption
- Research data\issue
- Data corruption
Another data point to consider is the rate of data change on a per database basis. If you have relatively stagnant data as compared to transactional data then backup and retention requirements would vary greatly. As such, evaluate your environment to have your backup, recovery and retention plan are in sync.
Another approach would be to rewind the last twelve to eighteen months of SQL Server database recoveries to serve as a guide for the types of needs in your organization. If you have not been able to meet all of the needs or if you have had failed recoveries, then another approach may be needed. Take a step back and figure it out what is required.
General Best Practices
Although an absolute set of rules do not exist, the reality is that at some level SQL Server backups are needed for recovery purposes. As such, it is critical to build a database backup plan as well as a database recovery plan. Once defined, then it time to build the scripts and processes to make the plan a reality. In addition, depending on the time and costs, third party products may offer a variety of benefits over native features.
Below outlines a base set of considerations for determining your retention period decision:
- Setup a SQL Server backup plan
- Establish a full backup schedule either on a multi-day, daily, weekly or monthly basis
- Incorporate differential backups into the plan to increase the available recovery points between full backups
- If additional points in time are needed, then consider transaction log backups as a solution to meet a finer level of granularity for recovery purposes
- Backup storage
- Retain the data locally on a RAID protected drive in order to have the shortest amount of recovery time
- Maintain the data on a centralized set of disks in order to recover the backups on another server if the production SQL Server encounters a critical issue
- Maintain the data off-site on disk drives or on tapes in order to recover the data in a circumstance when the primary site encounters a critical issue
- Prune the backups on a regular basis in order to balance the storage needs versus costs
- Tape backups should also be considered as a portion of the pruning process
- One option is to maintain a full month's worth of backups off site in case an issue arises
- Tape backups should also be considered as a portion of the pruning process
- Permanent tape retirement
- Although the tape costs can be high, it is advisable to permanently retain tape backups on a regular basis i.e. weekly, monthly or quarterly
- In many respects the tapes can be permanently retired or retained for a year and then recycled as a portion of the general tape pool
- Although the tape costs can be high, it is advisable to permanently retain tape backups on a regular basis i.e. weekly, monthly or quarterly
- Setup a SQL Server recovery plan
- Determine the recovery point
- Determine the data location
- Determine if the current hardware and software infrastructure will support the restore
- Determine the SQL Server instance and database that will be restored
- Validate the restore process is accurate
- Third party solutions
- Consider third party products for backup and recovery purposes to improve time, storage needs, security, etc.
- Research products that offer a continuous backup option or can provide an image of the databases as a point in time in an automated manner
- Organizations have emerged over the years that just manage off-site backups, so consider them as a option for maintaining off-site backup storage and recovery
Next Steps
- In many respects, too much attention is paid to the backup process and not enough time, concern and testing is allocated for the recovery process. As such, make sure your team and your management understands the recovery points based on your backup schedule and retention policies.
- If you are unsure about the requirements for your particular industry and have an idea that what you are doing today might not be sufficient based on other audits or internal policies, it is better to be safe than sorry. So talk to your team to clarify the backup, retention and recovery requirements.
- With legal requirements changing over the last few years in particular industries, if your backup, recovery and retention policies have not been re-evaluated recently, let this tip be the driver to check out your practices to see if they need to change.
- Be sure to inform your management that changing your backup retention period has a true cost from a few different perspectives. First is the tape cost to permanently retain particular tapes. Second is the realization that the tape devices, tape media and backup software change on a regular basis. As such, if you have to retain backups for a period of seven years, then you will need to keep in tape drives, servers and backup software functional for that time period as well. If not, how are you going to retain that data?
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
