Comprehensive data protection for all workloads
Post Reply
Recce
Novice
Posts: 4
Liked: never
Joined: Jan 18, 2016 12:16 am
Contact:

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

Post by 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
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: How can I get to the SQL Server transaction log backup f

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

Re: How can I get to the SQL Server transaction log backup f

Post by 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
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: How can I get to the SQL Server transaction log backup f

Post by veremin »

ccrichard
Enthusiast
Posts: 26
Liked: never
Joined: Sep 04, 2010 11:06 pm
Full Name: Richard Yamauchi
Contact:

Re: How can I get to the SQL Server transaction log backup f

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: How can I get to the SQL Server transaction log backup f

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

Re: How can I get to the SQL Server transaction log backup f

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: How can I get to the SQL Server transaction log backup f

Post by foggy »

Feel free to contact technical support if you feel it doesn't behave as expected.
ccrichard
Enthusiast
Posts: 26
Liked: never
Joined: Sep 04, 2010 11:06 pm
Full Name: Richard Yamauchi
Contact:

Re: How can I get to the SQL Server transaction log backup f

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: How can I get to the SQL Server transaction log backup f

Post by foggy »

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

Who is online

Users browsing this forum: Google [Bot], ybarrap2003 and 170 guests