How do you recover a SQL Database after CHECKDB fails

Availability for the Always-On Enterprise

How do you recover a SQL Database after CHECKDB fails

Veeam Logoby Recce » Tue Jan 19, 2016 9:54 am

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.
Recce
Novice
 
Posts: 4
Liked: never
Joined: Mon Jan 18, 2016 12:16 am

Re: How do you recover a SQL Database after CHECKDB fails

Veeam Logoby PTide » Tue Jan 19, 2016 11:01 am

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.
PTide
Veeam Software
 
Posts: 3248
Liked: 272 times
Joined: Tue May 19, 2015 1:46 pm

Re: How do you recover a SQL Database after CHECKDB fails

Veeam Logoby Recce » Mon Jan 25, 2016 2:54 pm

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
Recce
Novice
 
Posts: 4
Liked: never
Joined: Mon Jan 18, 2016 12:16 am

Re: How do you recover a SQL Database after CHECKDB fails

Veeam Logoby PTide » Tue Jan 26, 2016 10:47 am

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.
PTide
Veeam Software
 
Posts: 3248
Liked: 272 times
Joined: Tue May 19, 2015 1:46 pm

Re: How do you recover a SQL Database after CHECKDB fails

Veeam Logoby puran » Fri Aug 25, 2017 9:04 am

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.
puran
Lurker
 
Posts: 1
Liked: never
Joined: Fri Aug 25, 2017 8:50 am
Full Name: puran


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Google Feedfetcher, mkralj, opg70 and 1 guest