Creating a backup and restore policy based on service level agreements, Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO) is a critical part of DBA work. Another long-term but important Backup and Restore component is devising a Backup Retention Policy.
A Backup Retention Policy is defined as a set of rules outlining the steps of how long to retain a backup and the process of purging.
The broad categories influencing the Backup Retention Policy are based on : Legal\Compliance, Recovery Points and Cost.
As a first step identify a list of use cases within the organization. It is important to step back from the daily grind and look back to all the situations occurring in the last couple of years.
Some typical situations to consider are:
1) How to deal with backup and restore copy requirements in a project development life cycle longer than the immediate DR period, such as 2 years.
2) Managing\Differentiating retention policies of , for example, financial data , which may have legal implications versus session management servers
3) The current schedule backups the whole instance on the assumption the retention policy is based on per instance. Is data classification policy based on a per instance or per database? This impacts how database servers are built and maintained.
4) Retention of auxillary software , for example , recovering a db from 5 years ago – may require recovery of OS, fixpacks, passwords etc . Is the retention policy organised in a way to support a restore across a number of code and information sources
5) Purging of expired backups. Should they be automatic or manual?