SQL Server restore with transaction logs

[dschulz]

Hi,

is it possible with Veeam to restore a SQL Server database from an older restore point and apply all transaction logs that were archived afterwards up until the most recent transaction log backup?

When I look at the restore options when I select a restore point, I can only select transaction logs to restore between the restore point that happend before the chosen one and the next restore point afterwards.

The reason I ask: a customer of ours had a crash of his ESX infrastructure leading to a corruption of the file system of his SQL Server which went by unnoticed by the customer for a few weeks until corrupted data was being accessed (he didn't supervise the outcome of his database integrity checks...). We have gone through a lot of work to try to rescue his data, since repairing the data file was not possible.
All restore points of Veeam after this crashed have backuped up the corrupt data files, so they were unusable. Unfortunatly, they even have implemented a transaction log backup strategy, but forgot to switch their production database to recovery model "full".

To prevent this from happening again, it would be good to be able to restore a database like I have described above: restore an old restore point and apply all transsaction log backups up until the most recent point in time.
Any chance to implement this with Veeam or do we have to switch to backup transaction logs by using T-SQL to achieve this?

Thanks for you help, best regards,
Dirk

[foggy]

Hi Dirk, the case is clear, however, currently, Veam Explorer for SQL Server is not capable of such a restore functionality.

[dschulz]

ok, thank you for the quick answer.

I hope this feature will be implemented, as I can think of quite a lot use cases, especially when things get really wrong like in the case of our customer.

[foggy]

This seems to be the first time it is being requested so let's see if the demand grows with time.

[kaffeine]

To prevent this from happening again, it would be good to be able to restore a database like I have described above: restore an old restore point and apply all transsaction log backups up until the most recent point in time.

I actually think this would be indeed a useful feature for disaster scenarios as described above.

Here's +1 vote for its implementation!

Cheers

edit: this post from 2016 mentions this limitation as well Recovery of A SQL Database Using Transaction Logs in Veeam

[foggy]

Then it is not the first request, thanks for the heads up. Anyway, a pretty rare one to get prioritized. We will monitor the situation.

[dschulz]

my opinion: if you can't or don't implement this in the future, please at least update your documentation to reflect this restriction. Without knowledge of this restriction, one might falsely assume that a SQL Server backup including transaction logs with Veaam can be used to carry out a disaster recovery without data loss. This is only true if you can restore the latest full backup.
From the viewpoint of a disaster recovery, Veeam currently falls far behind even a simple maintenance plan backup managed by SQL Server Agent.

[foggy]

Thanks for sharing your opinion. Just keep in mind that even though Explorer cannot do this, you still can extract transaction logs from the backup files (with the help of support) and replay them using native SQL tools. Requires manual intervention but possible.

[dschulz]

good to know that it is technically possible.

My opinion stands: please include this into your documentation/help, as the knowledge about what is possible and what requires support from Veeam in case of a disaster recovery will definitely help to reduce the stress when you have to carry out a disaster recovery.

[lmbutault]

I agree that is we can play directly with Transaction logs, this should be placed into documentation.

During a POC we have made a validation of SQL restore through SQL explorer with transaction logs processing.

The thing is for a full VM recovery + SQL recovery, we have to go through a full VM restore then a full SQL restore with Explorer, so it is very long.
For a 1 TB DB it does not lead to acceptable times.

If we could make a full VM restore and after just replay Tlogs that would be the nice.

Same request is also for Oracle DB.

regards

[foggy]

Thanks for the feedback, guys. I will pass the documentation update request to the responsible team. Thanks!

[wayne7215]

Would wish that feature as well.....I was always expecting this would be already possible to restore to different snapshots backwards. We backup the transaction logs every 15 minutes and one of our DB admins has executed an wrong update command to the DB, so all records are garbage now and Veeam has already created another Transaction Log Backup because the 15 minutes cycle was reached already.
Now we can only restore the DB to the last state with already faulty records in, or we can restore to the VM Backup from last night and will lose almost 15 hours of records in the DB.
If Veeam is backing up the Transaction logs every 15 minutes, why it's not possible to restore the DB to a stage 1 hour ago? Shouldn't be a huge problem to implement this right?

[dschulz]

Thanks for the feedback, guys. I will pass the documentation update request to the responsible team. Thanks!

Hi,

has this been included within your documentation yet? I wasn't able to find it.

After more than one and a half year, this should be at least described within your docs.

Best regards

[mark49808]

+1 to this feature.

"you still can extract transaction logs from the backup files (with the help of support) and replay them using native SQL tools. Requires manual intervention but possible."

How can i do this?

[veremin]

As mentioned you need to reach our support first. This will allow you to have the logs files extracted from backups in the format acceptable by native SQL utilities. Thanks!

[mark49808]

I guess my question is then, can this be end-user accessible? i don't care if its some convoluted command-line. I just want the option without having to engage support.

[veremin]

Currently we have no intentions to make support utilities publicly available. Thanks!