How can I get to the SQL Server transaction log backup files

[Recce]

Hi,

I've had a request to do a point in time restore of a SQL Server database, but using the WITH STANDBY option. Basically the developers want to see the state of the database at different points in time so they can understand what went wrong with the application.

I can get the data and transaction log files to restore with, but I'm at a loss on where to find the transaction log backups so that I can apply them to bring the database up to the required point in time. Any thought / idea?

We are using B&R 8.0

Thanks in advance!!!

[veremin]

Are backing up transaction logs using VB&R? If so, feel free to use Veeam Explorer for MS SQL to bring a database to whatever point in time you want to. Thanks.

[Recce]

Using the Veeam Explorer will perform a point-in-time recovery WITH RECOVERY, but I don't believe it would be possible to do a point-in-time recovery WITH STANDBY using the Explorer.

Basically the Devs want to check the database data state at various points in time. So ideally we restore the database to a point-in-time at the start of the problem, WITH STANDBY, and give them the transaction log backup file. They can then perform successive transaction log restores, advancing the recovery point, allowing them to inspect the database over the time. When the database is in STANDBY, it's read-only and you can apply more transaction log restored.
The alternative is to do 20+ full point-in-time recoveries of a 200GB database.

Thanks in advance.

[veremin]

Hmm, in this case you can instantly recover SQL VM in isolated network and replay the corresponding logs on it, using VESQL. Thanks.

[ccrichard]

I have another justification for knowing where the VLB files are stored. I can find the files with the VBK/VIB/VRB files, however not all of them are there. The only VLB files that are there match the date of the latest VM Backup. Does Veeam keep these on the Veeam server somewhere until a VM backup occurs.

This concerns me because if my SAN fails and both my database server and my Veeam server on that SAN, then I could lose my transaction logs. Therefore I would need to copy the transaction logs manually somehow.
Can you provide how and where they are stored until they are saved to the backup repository?

[foggy]

Hello Richard, basically transaction logs are copied from VM to the repository (either directly or through the log shipping server) and should be kept according to their retention settings. Could you please check the job settings and verify transaction logs retention?

[ccrichard]

The are currently set to be kept until current image file backup is deleted. They are getting backed up every hour and I can see this in the reports/logs.
I'll try switching it to 15 days and see if it changes.

[foggy]

Feel free to contact technical support if you feel it doesn't behave as expected.

[ccrichard]

After a closer inspection I noticed that the .VLB files are growing After every transaction log backup. However, a very strange strange occurence is that the Datestamp does not update even though the file is growing. I wouldn't consider this a bug, more likely voodoo magic, that I don't know if conforms to the known universe. Otherwise, all is well.

[foggy]

Correct, data is appended to VLB file, however, I'd expect the modified date to be updated.