Recovery of A SQL Database Using Transaction Logs in Veeam

Availability for the Always-On Enterprise

Recovery of A SQL Database Using Transaction Logs in Veeam

Veeam Logoby IvanPiacun » Mon Jun 06, 2016 9:49 pm

This question relates to Veeam Backup and Replication V9.0.0.1491 and Case id #01819616

How can you recover from a database corruption discovered by CheckDB without loss of transactions, loss of data, nor impact to availability of other databases on the same SQL Server which has been backed up with a daily Veeam backup and with process transaction logs with this job specified with log backups for every 15 minutes, when it is not possible to do tail of log backup in Veeam, nor integrate a tail of log backup taken with SQL into a Veeam restore and it is not possible to restore a database backup from a backup job execution earlier than the current execution of the backup job and roll forward on all logs from all executions of the job since then including a tail of log backup?
Ivan Piacun MIITP CITPNZ
IvanPiacun
Novice
 
Posts: 7
Liked: 2 times
Joined: Tue Apr 19, 2016 3:46 am
Location: AgResearch Ltd, Hamilton, New Zealand
Full Name: Ivan Piacun MIITP CITPNZ

Re: Recovery of A SQL Database Using Transaction Logs in Vee

Veeam Logoby PTide » Tue Jun 07, 2016 11:46 am

Hi,

How can you recover from a database corruption discovered by CheckDB without loss of transactions, loss of data, nor impact to availability of other databases on the same SQL Server
You can use Appliation Item restore feature in order to replay transaction log and see if that fixes the issue. That operation will be performed only on the specified database. If the corruption was caused, for example, by memory or power failure then replaying logs should help you to recover the database to the state when the latest log backup was performed, with minimal loss of data. If you wish to recover from the tail of log that has not been copied by Veeam yet then you have to apply SQL transaction logs to the most recent db restore point manually.

The best option would be to perform all these operation on another server - just restore your db to another machine or use Instant Recovery feature, as support engineer has already suggested.

Thanks
PTide
Veeam Software
 
Posts: 3019
Liked: 246 times
Joined: Tue May 19, 2015 1:46 pm

Re: Recovery of A SQL Database Using Transaction Logs in Vee

Veeam Logoby IvanPiacun » Tue Jun 07, 2016 9:17 pm

Unfortunately application item restore in order to replay transaction logs does not work when the database to restore is one or more backup job executions prior to the current back up job execution as in my post you will see Veeam does not allow the restore of logs from multiple backup job executions. Yes I agree you can recover If you apply tail of log and backup logs manually which is what we do when using SQL as our backup for SQL Databases however we were hoping to replace SQL backups and SQL log backups with Veeam doing this work so that we would not need to duplicate it, and to avoid Veeam backups affecting SQL backups due to Veeam VSS processing.
Ivan Piacun MIITP CITPNZ
IvanPiacun
Novice
 
Posts: 7
Liked: 2 times
Joined: Tue Apr 19, 2016 3:46 am
Location: AgResearch Ltd, Hamilton, New Zealand
Full Name: Ivan Piacun MIITP CITPNZ

Re: Recovery of A SQL Database Using Transaction Logs in Vee

Veeam Logoby IvanPiacun » Tue Jun 07, 2016 9:42 pm

Hi PTide

Your comment "The best option would be to perform all these operation on another server - just restore your db to another machine or use Instant Recovery feature, as support engineer has already suggested."

Is not correct.

It is not always possible to repair errors shown by DBCC CheckDB without loss of data as was the case in this instance. That is why in the past we have used SQL Backups and SQL Log Backups with a tail of log backup in this situation which allows you to recover from the corruption without loss of data. Your solution would also lose transactions if the database was left online at the point of the last backup and transactions where done since then, as applying a DBCC Repair on a VM in instant recovery and copying the fixed database from there would result in loss of transactions due to the database still being in use.

Thus Veeam cannot "Just work" to recover SQL Databases that have been corrupted, due to the reasons I have already given in my original post unless there is something else that you are aware of, so we cannot use Veeam for log backups and reliably restore back to a point prior to the current backup and roll forward on logs since then including a tail of log backup when a DBCC CheckDB run on a weekend discovers a database corruption we need to go back to a point when the corruption was not present i.e. Prior to the previous weekend.

So I take it we should not use Veeam for recovery from corrupt databases but continue to rely on SQL Backups and Log backups as Veeam, just does not work in this case.
Ivan Piacun MIITP CITPNZ
IvanPiacun
Novice
 
Posts: 7
Liked: 2 times
Joined: Tue Apr 19, 2016 3:46 am
Location: AgResearch Ltd, Hamilton, New Zealand
Full Name: Ivan Piacun MIITP CITPNZ

Re: Recovery of A SQL Database Using Transaction Logs in Vee

Veeam Logoby IvanPiacun » Tue Jun 07, 2016 10:33 pm

Hi Again PTide
Further Re Your comment "The best option would be to perform all these operation on another server - just restore your db to another machine or use Instant Recovery feature, as support engineer has already suggested."
Further context of the engineer's comment. He suggested use of ChecKDB Repair on another machine using instant recovery and then copying the database back. Which as I said can result in loss of data which is the case in this instance.

Kind regards
Ivan Piacun MIITP CITPNZ
Ivan Piacun MIITP CITPNZ
IvanPiacun
Novice
 
Posts: 7
Liked: 2 times
Joined: Tue Apr 19, 2016 3:46 am
Location: AgResearch Ltd, Hamilton, New Zealand
Full Name: Ivan Piacun MIITP CITPNZ

Re: Recovery of A SQL Database Using Transaction Logs in Vee

Veeam Logoby PTide » Wed Jun 08, 2016 10:40 am

Hi Ivan,

<...>Is not correct.

It is not always possible to repair errors shown by DBCC CheckDB without loss of data as was the case in this instance. That is why in the past we have used SQL Backups and SQL Log Backups with a tail of log backup in this situation which allows you to recover from the corruption without loss of data.<...>
I believe that some misunderstanding is involved here. I never said there would be no loss of data at all, I said there would be minimal loss of data. Instant Recovery is just fast and easy way to get a testing range for recovery procedures so you don't make harm to your production in case something goes terribly wrong, that's what I meant when offered you to use Instant Recovery in conjunction with SQL restore. Sorry for confusion.

Unfortunately application item restore in order to replay transaction logs does not work when the database to restore is one or more backup job executions prior to the current back up job execution as in my post you will see Veeam does not allow the restore of logs from multiple backup job executions.
Correct. We will take a closer look at this limitation in order to improve the way how SQL point-in-time restore procedure works now. Thank you for pointing that out.

With that said I believe that you should stick with using native SQL tools, indeed, due to the lack of needed functionality in the current version of VBR. Your note about tail log has been counted as a feature request.

Thank you.
PTide
Veeam Software
 
Posts: 3019
Liked: 246 times
Joined: Tue May 19, 2015 1:46 pm

Re: Recovery of A SQL Database Using Transaction Logs in Vee

Veeam Logoby CarstenE » Mon Aug 08, 2016 2:34 pm

This reply relates to Case id #01819616

Experienced the described situation.
Thus I must insist on a Feature Request for the limitaion
Veeam does not allow the restore of logs from multiple backup job executions

Setup:
    Full backups every 12 hours at 06:00 and 18:00.
    Log backup every hour.
The situation was:
    Thursday morning SQL Server started to log logical consistency-based I/O error (error 824) in the errorlog.
    Friday afternoon the errors was discovered.
    The application server was stopped Friday short before 18:00 and we waited for the next log backup to finish.
The errors was in a single data/ndf file and not in the transaction log, so normally (as when we used native backup tools) we would have restored a full backup from Wednesday 18:00 and applied all subsequent logs. By doing this we would have been able to restore the database without data loss at all.
Due to the limitaion this was not possible.

We did a test restore covering Thursday morning to prove restore was possible without data loss. The test restore was based on the full backup from Wednesday 18:00 with logs applied until Thursday 18:00 (This was possible due to a missing restore point Thursday 06:00 !?). DBCC CHECKDB showed no errors in the restored database.

Brgds, Carsten

PS/End of history: After a "DBCC CHECKDB ... REPAIR_ALLOW_DATA_LOSS" the databasen showed a corrupt index and corruption in another table. The index was rebuild but a lot of data was lost in the table. Fortunately the table was a table containing (non important) logging/history information. So it was decided to continue production with the loss of data in the logging/history table.
CarstenE
Lurker
 
Posts: 1
Liked: never
Joined: Mon Aug 08, 2016 1:14 pm
Full Name: Carsten Eiberg

Re: Recovery of A SQL Database Using Transaction Logs in Vee

Veeam Logoby ccrichard » Fri Sep 16, 2016 4:51 pm

Forgive my lack of experience, and I'm probably misunderstanding, but is there no way after the initial recovery to set the database back to recovery mode?
What if you immediately take a backup of the database and then do another restore with no_recover? Could you not then apply transaction log?
ccrichard
Enthusiast
 
Posts: 26
Liked: never
Joined: Sat Sep 04, 2010 11:06 pm
Full Name: Richard Yamauchi

Re: Recovery of A SQL Database Using Transaction Logs in Vee

Veeam Logoby IvanPiacun » Fri Sep 16, 2016 10:50 pm

I'm sorry but you have misunderstood the issue. The database is in full recovery mode and remains and needs to remain in full recovery mode throughout the process. The problem has noting to do with reestablishing Full Recovery mode.
Ivan Piacun MIITP CITPNZ
IvanPiacun
Novice
 
Posts: 7
Liked: 2 times
Joined: Tue Apr 19, 2016 3:46 am
Location: AgResearch Ltd, Hamilton, New Zealand
Full Name: Ivan Piacun MIITP CITPNZ


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Bing [Bot], Google Feedfetcher and 26 guests