How do you recover a SQL Database after CHECKDB fails

[Recce]

Hi,

Veeam is coming to the company I work for so I'm doing some prep-work without having access to the application yet.
I'm wondering how I would recover a SQL Server database after the database has suffered a corruption (i.e. it's failing CHECKDB and the database corruption involves one or more of the tables in the database).

With native SQL backups I would do a final backup of the transaction log, restore the full database backup taken before the last successful CHECKDB, and then apply all the transaction logs backups to bring the database up-to-date. The important thing is that I'm not using a Full backup that contains the database corruption. I can see how to do a straight up point-in-time recovery which I assume will use the most recent full backup, but I can't see how to control which Full backup Veeam will use.

Thanks in advance.

[PTide]

Hi,

As it is stated in our helpcenter article:

Database will be recovered to the closest VM restore point before the moment you specify on the timeline, and then transaction log replay will bring the database to the necessary state.

In other words, you specify the desired point in time to recover to, DB server VM gets reverted to the closest restore point available before a corruption occured, after that transaction logs are applied. Does this answer your question?

Thank you.

[Recce]

Hi PTide,

thanks for the reply, I've read the article and I also now have access to the Veeam software (v8).

We are using application aware processing and doing a weekly full and daily incremental synthetic backup. In your reply you state that I select the point in time to recover to (ideally "now") and the "DB server VM gets reverted to the closest restore point available before a corruption occurred". How is Veeam aware that corruption to the to the DB occurred? Does it use the dbi_dbccLastKnowGood value on the database?

For example, if we do a full backup and CHECKDB every night, and discover on Thursday morning that CHECKDB showed corruption, I could select the most recent restore point (Wednesday night), open the Veeam Explorer for SQL Server and Veeam would ignore the Wednesday night full database backup and use the Tuesday night full database and roll forwards?

Thanks

[PTide]

daily incremental synthetic backup

I'm not sure if I follow you...incremental and synthetic are mutually exclusive things in Veeam terminology. Could please clarify that a little bit? What did you choose in job options?

How is Veeam aware that corruption to the to the DB occurred?

To make an transactionally-consistent backup Veeam leverages sql internal mechanisms. It means that if your engine allows processing of transaction logs when in corrupted state then you'll need to choose point in time manually. If your database engine does not allow log processing on a corrupted instance, then point in time recovery scale will contain only non-corrupted RPs.

if we do a full backup and CHECKDB every night, and discover on Thursday morning that CHECKDB showed corruption, I could select the most recent restore point (Wednesday night), open the Veeam Explorer for SQL Server and Veeam would ignore the Wednesday night full database backup and use the Tuesday night full database and roll forwards?

Correct. First Veeam restore your VM to the most recent RP and then applies transaction logs in order to get DB as close as possiblle to the last known healthy state.

[puran]

Another way to recover a SQL database after checkdb fails is SQL Database Repair which can easily repair a corrupted mdf file. here is the tested review of this SQL database repair tool by SQL MVP Pinal Dave.