SQL Server backup and recovery
When did you last prove that your SQL Server backup can actually be restored?
A backup job that reports success tells you a file was written. It doesn’t tell you the database can be recovered, to the right point, in the time the business can wait.
What we review
From backup job to recovered database
- Backup reviewWhich databases are backed up, by what, how often and with what options, from
msdbbackup history rather than job names. - Restore testingReal restores to a separate server, followed by an integrity check, with the time each one took.
- RPO and RTOHow much data loss and downtime each system can accept, and whether the current setup meets it.
- Transaction log configurationRecovery models that match the backups taken, log backup frequency and log growth.
- RetentionHow far back you can go, and whether that matches operational, contractual and legal needs.
- Off-site strategyCopies held away from the server and its storage, and protection from being deleted or encrypted along with it.
- Disaster recovery planningA written, tested sequence for rebuilding SQL Server and its databases, logins, jobs and linked settings elsewhere.
- Third-party and VM-level backupsWhether image or appliance backups produce consistent, restorable SQL Server databases and keep the log chain intact.
Gaps we commonly find
- Databases in FULL recovery with no log backups, so logs grow until a disk fills
- A backup routine that silently misses databases added since it was set up
RESTORE VERIFYONLYtreated as proof of recoverability; it checks the backup is readable, not that the database restores and is consistent- Backups kept only on the same server or storage as the databases
- Nobody sure how long a full restore of the largest database would take
- Logins, SQL Agent jobs and certificates needed after a restore not backed up anywhere
Further reading How often should SQL Server backups be tested?
Further reading Why SQL Server transaction logs keep growing
What you get
- A clear statement of what can be recovered today, to what point, and roughly how quickly
- The gaps between that and what the business needs, in priority order
- A corrected backup and log configuration, implemented by us or your team
- Restore test results you can show an auditor, insurer or customer
- A documented recovery procedure, and a schedule for testing it again
Ask for a fixed-price assessment. We’ll give you a fixed price after a short conversation.
Questions
Backup and recovery questions
What do RPO and RTO mean for SQL Server?
The recovery point objective is how much recent data you could afford to lose; for SQL Server it is mostly set by how often transaction log backups run. The recovery time objective is how long you can be without the system; it depends on database size, where the backups are and how practised the restore is.
Our backup software says every job succeeded. Isn’t that enough?
It shows the job finished. Only restoring the backup, and checking the restored database, shows that you could recover from it. Restore tests also tell you how long recovery takes, which is often the surprise.
Will restore testing affect production?
Tests are restored to a separate server or instance, so production isn’t touched. Copying large backup files can use network and storage capacity, so we schedule it with your team.
Next step
Not sure your backups would restore?
Tell us how backups are taken today and how much data and downtime the business could accept.