SQL SERVER – Check Backup Reliability

Backup Reliability needs a successful restore followed by integrity and application validation. Customers ask whether they can skip that exercise.

1. Restore to an isolated test database with distinct file paths, preserving the production database.
2. Include the required full, differential and log sequence to the intended recovery point.
3. Supply required encryption certificates or keys and validate server-level dependencies.
4. Run the appropriate integrity checks and reconcile important application data and behavior.
5. Record elapsed recovery time and repeat tests as the recovery plan changes.

RESTORE VERIFYONLY checks completeness, readability and available checksums. It does not restore the database. It also does not fully validate database structure or business meaning. Use it as a preliminary check.

I previously mentioned production or development as restore targets. A separate controlled recovery environment avoids overwriting a working production database. Repeatable restore testing provides stronger evidence. Successful backup commands, file existence and file size don’t establish the recovery objective.

Keep test evidence, required media and keys together in the recovery plan. Validate the intended restore sequence. Confidence comes from demonstrated recovery.

Reference: VERIFYONLY capabilities and limits.

Related reading

• Comprehensive Database Performance Health Check
• Pinal Dave on YouTube
• Zoom in SSMS – SQL in Sixty Seconds #153
• Transfer Schema of Table – SQL in Sixty Seconds #152
• Find a Table in Execution Plan – SQL in Sixty Seconds #151
• Transactions and Variables – SQL in Sixty Seconds #150
• Count Table in Cache – SQL in Sixty Seconds #149
• List All Sessions – SQL in Sixty Seconds #148
• Line Numbers for SSMS Efficiency – SQL in Sixty Seconds #147
• Slow Running Query – SQL in Sixty Seconds #146
• Change Database and Table Collation – SQL in Sixty Seconds #145
• Infinite Loop – SQL in Sixty Seconds #144
• Efficiency Trick – Query Shortcut – SQL in Sixty Seconds #143
• SQL SERVER – 16 CPU vs 1 CPU : Performance Comparison – SQL in Sixty Seconds #142
• SQL SERVER – TOP and DISTINCT – Epic Confusion – SQL in Sixty Seconds #141

A readable backup set is not demonstrated recoverability, it is one prerequisite for a tested recovery.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.