Comprehensive data protection for all workloads
Post Reply
IvanPiacun
Influencer
Posts: 11
Liked: 2 times
Joined: Apr 19, 2016 3:46 am
Full Name: Ivan Piacun MIITP CITPNZ
Location: AgResearch Ltd, Hamilton, New Zealand
Contact:

Recovery of A SQL Database Using Transaction Logs in Veeam

Post by IvanPiacun »

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
PTide
Product Manager
Posts: 6408
Liked: 724 times
Joined: May 19, 2015 1:46 pm
Contact:

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

Post by PTide »

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
IvanPiacun
Influencer
Posts: 11
Liked: 2 times
Joined: Apr 19, 2016 3:46 am
Full Name: Ivan Piacun MIITP CITPNZ
Location: AgResearch Ltd, Hamilton, New Zealand
Contact:

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

Post by IvanPiacun »

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
Influencer
Posts: 11
Liked: 2 times
Joined: Apr 19, 2016 3:46 am
Full Name: Ivan Piacun MIITP CITPNZ
Location: AgResearch Ltd, Hamilton, New Zealand
Contact:

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

Post by IvanPiacun »

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
Influencer
Posts: 11
Liked: 2 times
Joined: Apr 19, 2016 3:46 am
Full Name: Ivan Piacun MIITP CITPNZ
Location: AgResearch Ltd, Hamilton, New Zealand
Contact:

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

Post by IvanPiacun »

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
PTide
Product Manager
Posts: 6408
Liked: 724 times
Joined: May 19, 2015 1:46 pm
Contact:

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

Post by PTide »

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.
CarstenE
Lurker
Posts: 1
Liked: never
Joined: Aug 08, 2016 1:14 pm
Full Name: Carsten Eiberg
Contact:

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

Post by CarstenE »

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.
ccrichard
Enthusiast
Posts: 26
Liked: never
Joined: Sep 04, 2010 11:06 pm
Full Name: Richard Yamauchi
Contact:

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

Post by ccrichard »

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?
IvanPiacun
Influencer
Posts: 11
Liked: 2 times
Joined: Apr 19, 2016 3:46 am
Full Name: Ivan Piacun MIITP CITPNZ
Location: AgResearch Ltd, Hamilton, New Zealand
Contact:

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

Post by IvanPiacun »

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
Influencer
Posts: 11
Liked: 2 times
Joined: Apr 19, 2016 3:46 am
Full Name: Ivan Piacun MIITP CITPNZ
Location: AgResearch Ltd, Hamilton, New Zealand
Contact:

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

Post by IvanPiacun »

This problem still appears to be present in Veeam 9.5 update 2 after extensive testing I have just completed.


This problem of recovering a SQL database using Transaction logs after DBCC CHECKDB has discovered corruption has two issues as I see it:

1) Being able to specify on the Veeam Restore to start from N Full Backups prior and roll forward on all the logs since then.
2) Being able to specify a special log backup for a database which does a tail of log backup. This can be worked around at present by making the database read only and then waiting for the next log backup to be done, but is not tidy for users of the database.
Ivan Piacun MIITP CITPNZ
IvanPiacun
Influencer
Posts: 11
Liked: 2 times
Joined: Apr 19, 2016 3:46 am
Full Name: Ivan Piacun MIITP CITPNZ
Location: AgResearch Ltd, Hamilton, New Zealand
Contact:

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

Post by IvanPiacun »

Response from Jonathan Calovski:
As a workaround until enhancement are complete:
They could add their script to do the “DBCC CHECKDB” and SQL log backup as a task that runs right before the VM is backed up, this way it’s all in the one location. They could use U-AIR to do the recoveries instead of the Veeam Explorer.
Ivan Piacun MIITP CITPNZ
IvanPiacun
Influencer
Posts: 11
Liked: 2 times
Joined: Apr 19, 2016 3:46 am
Full Name: Ivan Piacun MIITP CITPNZ
Location: AgResearch Ltd, Hamilton, New Zealand
Contact:

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

Post by IvanPiacun »

Hi Jonathan

The way I understand Veeam pre-Freeze scripts work that would mean the entire backup job would not run after the problem was detected by script which was reported by DBCC CHECKDB.
The Log backup taken by the script would not be integrated with the application aware log backups and hence could need be restored in sequence in the Veeam database restore.

I understand U-AIR creates an isolated SureBackup Job from which files can be accessed and SQL Server can be accessed. I don’t understand how this could be used for a full restore of a database rolling forward on logs, or do you mean to imply that the regular log backups since the previous full backup would continue being run and the restore could be done from the last full backup rolling forward on the logs by Veeam self-service restore?

There is also a problem in that while the pre-freeze script is executed the backup cannot be stopped until it times out of its own accord, or it completes, and this timeout would have to be set pretty high to allow time for the DBCC CHECKDB to run on all the databases.

Can you please let me know how to increase the time out period in Veeam 9.5 Update 2 as 10 minutes will definitely not be enough.
Ivan Piacun MIITP CITPNZ
IvanPiacun
Influencer
Posts: 11
Liked: 2 times
Joined: Apr 19, 2016 3:46 am
Full Name: Ivan Piacun MIITP CITPNZ
Location: AgResearch Ltd, Hamilton, New Zealand
Contact:

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

Post by IvanPiacun »

Hi Ivan,

I’ve had further commentary from our Senior Systems Engineer that are copied below (not please do update your original forum post for R&D team):

The way I understand Veeam pre-Freeze scripts work that would mean the entire backup job would not run after the problem was detected by script which was reported by DBCC CHECKDB.
We have 2 script integration points within backup jobs.
1)Storage -> Advanced -> Scripts
These scripts run before or after the whole backup job.
In the case of a failure, the job would not continue.
2)Guest Processing -> Applications -> Scripts
These scripts run before or after the backup of the specific VM.
In the case of a failure the VM would not be backed up, but the job would continue.

The Log backup taken by the script would not be integrated with the application aware log backups and hence could need be restored in sequence in the Veeam database restore.
This is correct. It is a work around, to get the backups in one place, instead of managing them separately. It does not address the capabilities that you would like to see added into the Veeam Explorer for SQL.

I understand U-AIR creates an isolated SureBackup Job from which files can be accessed and SQL Server can be accessed. I don’t understand how this could be used for a full restore of a database rolling forward on logs, or do you mean to imply that the regular log backups since the previous full backup would continue being run and the restore could be done from the last full backup rolling forward on the logs by Veeam self-service restore?
For the moment, the Veeam Explorer for SQL isn’t meeting you requirements, and I think that you may find U-Air more flexible.

You could use U-Air to power up a copy of the good DB, use the SQL Management Studio in the virtual lab then apply the transaction logs (either from the SQL transaction log backup or from the other image based backups and the current tail) then take that whole DB and restore it back. This is all manual and in fact mirrors the current process, but doing it in the virtual lab and confirming it’s going to work as desired before moving the DB back to prod, may be helpful. Basically where U-Air helps is by taking the process of rolling forward the logs away from production, without creating a separate DB and taking up double the space.

There is also a problem in that while the pre-freeze script is executed the backup cannot be stopped until it times out of its own accord, or it completes, and this timeout would have to be set pretty high to allow time for the DBCC CHECKDB to run on all the databases.

Can you please let me know how to increase the time out period in Veeam 9.5 Update 2 as 10 minutes will definitely not be enough.
I believe that there are registry keys available to change the timeout settings for both types of scripts, but support would know more.

Best regards,
Jonathan Calovski

Inside Channel Manager
– New Zealand & Islands
Ivan Piacun MIITP CITPNZ
puran
Novice
Posts: 5
Liked: 1 time
Joined: Aug 25, 2017 8:50 am
Full Name: puran
Contact:

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

Post by puran »

here is a article which helps you to Recover Data from Log File in SQL Server even if the database .mdf file has been corrupted.
IvanAPiacun
Lurker
Posts: 1
Liked: never
Joined: Jun 15, 2021 11:33 pm
Full Name: Ivan A. Piacun MIITP CITPNZ
Contact:

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

Post by IvanAPiacun »

Hi Puran, The article you mention could involve analysing hundreds of log files especially if they are taken every say 15 minutes. Then it would be necessary to write a TSQL Script to apply the updates in time order to update the relevant tables in the database in question. Recovery time could be quite condiderable.
Post Reply

Who is online

Users browsing this forum: Bing [Bot], Semrush [Bot] and 273 guests