SUSPECT database

Recently I had a customer case with a “suspect” database. In that state database is not accessible (not online), and users could not work. First thing to look is the SQL error log. There we could see a time when IO errors occured that got database corrupt, and that the error was on a DATA file. That was 5 days ago, so recommendation of one of DBAs was to restore to 6 days old backup and accept 6 days of data loss.

Wait a minute – 6 days of data loss???

That is a huge data loss. Since IO errors occured on a data file, yes, we should use FULL and DIFF backups PRIOR to first IO error, sure. But then we can apply transaction log backups from there and roll forward until the last log backup. And that last log backup was taken just before database became suspect (inaccessible), maybe few seconds apart. Restoring that way, I was able to recover all data with close to zero data loss. It could also be zero if in these few seconds were no data entry, but even if it is it is a small loss compared to 6 days of data loss.

Summary

Do NOT restore to X days old backup and leave it there. Use transaction log backups to roll forward to as recent as possible point in time to minimize data loss. Make sure you have transaction log backup RETENTION set to at least 5 days (preferably 7-14), to be able to do this. Do a regular and automated “restore test” that makes sure your backups are solid, including transaction log backups, not just full/diff.