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

Availability for the Always-On Enterprise

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

Veeam Logoby Recce » Wed Aug 31, 2016 10:52 pm

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!!!
Recce
Novice
 
Posts: 4
Liked: never
Joined: Mon Jan 18, 2016 12:16 am

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

Veeam Logoby v.Eremin » Thu Sep 01, 2016 9:32 am

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.
v.Eremin
Veeam Software
 
Posts: 13255
Liked: 968 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

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

Veeam Logoby Recce » Thu Sep 01, 2016 11:19 am

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.
Recce
Novice
 
Posts: 4
Liked: never
Joined: Mon Jan 18, 2016 12:16 am

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

Veeam Logoby v.Eremin » Thu Sep 01, 2016 11:34 am

v.Eremin
Veeam Software
 
Posts: 13255
Liked: 968 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

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

Veeam Logoby ccrichard » Thu Sep 15, 2016 8:15 pm

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

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

Veeam Logoby foggy » Fri Sep 16, 2016 10:29 am

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?
foggy
Veeam Software
 
Posts: 14716
Liked: 1075 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

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

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

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

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

Veeam Logoby foggy » Fri Sep 16, 2016 4:57 pm

Feel free to contact technical support if you feel it doesn't behave as expected.
foggy
Veeam Software
 
Posts: 14716
Liked: 1075 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

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

Veeam Logoby ccrichard » Fri Sep 16, 2016 7:47 pm

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

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

Veeam Logoby foggy » Mon Sep 19, 2016 2:56 pm

Correct, data is appended to VLB file, however, I'd expect the modified date to be updated.
foggy
Veeam Software
 
Posts: 14716
Liked: 1075 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Dima P., vClintWyckoff, vishwajeeth and 30 guests