“Assumption is the mother of all xxxx ups” is a great quote from Under Siege 2. It summarises perfectly assumptions about last known good backups.
An application owner contacted me this week. He requested an immediate Restore of 3 databases from a specific date ,he’d corrupted during an application install. I’ve never had a problem restoring from this backup system, but like any system it requires rigorous governance and testing rigour.
Regular review of backup retention policy
A recent retention policy adjustment changed the last active database setting. In theory , based on the documented details of the retention policy, those backups should have been available. But they weren’t.
We recovered the system in a different way, but the point is still relevant about checking for last known good backups.
Moral of the story : regardless of how incredible your backup system, allow for reporting on databases that don’t have a backup suitable to Recovery Point Objective (RPO).
A regular SQL Server backup review with Powershell – is a good starting point. Also , in large environments I try and standardise , which keeps the variables to a minimum.
Read more on Regular SQL Server Backup Review