Transaction Logs in Backup Copy or Replica

[akrimmer]

I would like to see a method to copy SQL transaction logs to a DR site either using a replica job or backup copy job. Currently this is not available since neither of them are application aware.

The goal is to have local SQL backups with 30 minute T-Log backups, daily SQL replica jobs to our DR site, and some method to copy the T-Logs to the DR site to get the replica current in the event of a failover. Currently, if we had to failover to our replica, we could be failing over the last nightly replica that ran and could be anywhere from 1-23 hours behind in our SQL data. We need way to be able to replay those T-Logs at the DR site to get the replica current.

Thanks,

[Dima P.]

Hello and welcome to Veeam forums,
Thank you for detailed explanation – we will look into this.

[Greg.Bean-DeFlumer]

Support case #715622

[rp.TICnine]

Any update about this request?

Thanks in advance!

[foggy]

What kind of update are you looking for? If you're interested in this functionality, then your voice is counted. We collect all the feedback but prioritize features based on the number of requests and value to the product.

[rp.TICnine]

Sure! I'm really interested in this feature! Count on me for the request.

[OmiFreak]

We would need this function as well. SQL log backup just in on location is not sufficient.

[PinnaclePatron]

I would also like to see a method of updating the remote SQL replica with transaction log backups.

[ingvargo]

I am also intererested to have this function in copy jobs.

[jim3cantos]

We would need this function as well. SQL log backup just in on location is not sufficient.

+1

[jim3cantos]

I would also like to see a method of updating the remote SQL replica with transaction log backups.

supposing that transaction logs are accessible from replication site:

1) In the event of a failover of SQL server to the replica, veeam explorer will be able to apply logs from original SQL server to replica without problems? I suppose yes, but better to confirm.

2) any "easy" way to test point 1? Could be with a surebackup job with replicas of Veeam server, SQL server and SQL logs repository in the app. group?

[foggy]

Currently logs grabbed during the backup can be replayed on the database from their corresponding parent VM backup only. No way to apply them to replica VM.

[jim3cantos]

Currently logs grabbed during the backup can be replayed on the database from their corresponding parent VM backup only. No way to apply them to replica VM.

I think I have done test in point 1 above without problems in a DEV SQL server: planned failover + logs (from backup) applied to active replica + failback. Am I misunderstanding something?

[Infin3ty]

Hello there,

I'm looking for some advice in the following:
We have an SQL server and a Exchange Server, we would like to replicate these servers once a day to an other datacenter for Failover purpose.

In case of an Emergency, we would like to use the replicated VM and restore the latest Transaction logs so the dataloss is minimal.

Next, We would also like to use Failover Plans to automate on and another.

What is best practice in this one?
Thanks in advance

[foggy]

I would appreciate if Jose could describe the way he was able to do what he has mentioned above. Most likely he had to use native tools for logs replay on replica VM.

[jim3cantos]

I would appreciate if Jose could describe the way he was able to do what he has mentioned above. Most likely he had to use native tools for logs replay on replica VM.

No special procedure. As indicated above: just stop SQL logs backup job, planned failover of SQL server and, with the replica active, restore to latest state using Veeam SQL explorer. When restore finishes, then failback to production.

I don't just only apply logs but restore the database to the state needed with SQL explorer. For this to be operational in case of disaster, the SQL logs backup job repository has to be in DR site (same as replicas...and Veeam server).

[kevinnaughton]

Please add me to this feature request list. I just advised our DBA of the ability to perform the log backups via Veeam, and his first question is how we would apply those to the replica. To which I said

[nengelberth]

I've been working with one of our end users on getting their SQL servers converted to use Veeam backups, to be consistent with their standard backup strategy. I've been working with support (Case # 00976504) to get assistance with backup configuration options, and am generally pleased with the response time and knowledgeability of my support agent, Michael.

However, I recently uncovered (via trial and error on my test system) that the hourly SQL Log backups did not appear to be copying with the Backup Copy job we have set up for this system. Michael confirmed that the copy job won't grab the log backups, and suggested a file copy. When I asked how the restore process would work if we lost the main site altogether (e.g. a fire), I was told "the SQL log backups produced by Veeam will only work with the backup job they are associated with and not the backup copy job, even if they are copied offsite with the backup copy." This is a huge problem for us, as it means we cannot meet our 1 hour RPO for these SQL servers.

I was directed here to make a feature request for having SQL Log files available in the offsite copy. But honestly, I feel like this is a show-stopping lack of functionality, more along the lines of a critical bug! Without the offsite backup having a copy of my SQL Transaction Log backups that can be used in a restore operation, I cannot meet any RPO shorter than 1 day using Veeam's Application Aware SQL backups. I don't have any on-premise Exchange servers being backed up with Veeam, so I can't confirm whether or not the same limitation exists for that Application Aware backup mode, but if it does, I believe you need to resolve it ASAP.

[veremin]

Hi, Noah,

We've got similar requests already. Your voice for the mentioned functionality is counted.

Thanks.

[ahahum]

We're planning on switching over to Veeam for our SQL transaction log backups/truncation. Is it possible to replicate those offsite? If so, how?

[foggy]

Adam, transaction logs can be replayed only on the corresponding backups, so it depends on how you're going to replicate them. You need to keep the entire original backup chain to be able to apply them (so backup copy job is not an option).

[ahahum]

Thanks for the reply.

We're currently using SAN based replication and we're likely switching to Veeam's replication. I suppose a more specific question is once we begin replicating the VM with Veeam, what about the transaction logs? If they're still at my primary site only, I'd like to have them at my DR site as well. We're not sure how frequently we'll be replicating (at least once daily for sure) so we'd like the ability to have those log backups at the DR site if necessary.

[foggy]

If you're talking about Veeam B&R replication jobs, then transaction logs backup is not available there, you can only truncate logs there. Transaction logs backup is a backup job feature.

[ahahum]

The backup job truncates and backs the logs up to the backup repository - that's my understanding of it - is that inaccurate?

Once they're on the backup repository, they're not on the VM any longer - right? Which means the replicated VM would not have the logs - this means I would be limited to only the full restore point at the DR site. I would like to have the log backups (that reside on the backup repository) available at the DR site as well. This would allow me to do a point in time restore if necessary.

If we blow up at primary and the restore point is 12 hours old, that does me no good at DR. I need to be able to restore back to an exact time/transaction at both primary and DR sites. Does that make sense what I'm going for?

Thanks for your help!

[foggy]

The backup job truncates and backs the logs up to the backup repository - that's my understanding of it - is that inaccurate?

That's correct.

If we blow up at primary and the restore point is 12 hours old, that does me no good at DR. I need to be able to restore back to an exact time/transaction at both primary and DR sites. Does that make sense what I'm going for?

That makes perfect sense, however, currently to achieve that you'd need to copy the entire original backup chain to the DR site as well (transaction log backups are stored in the same repository folder along with the regular backup files as files with .vlb extension). You cannot apply the log files to replica VM upon restore.

[ahahum]

That makes perfect sense, however, currently to achieve that you'd need to copy the entire original backup chain to the DR site as well (transaction log backups are stored in the same repository folder along with the regular backup files as files with .vlb extension). You cannot apply the log files to replica VM upon restore.

Any suggestions on how I can make this happen with a creative workaround? If not, I guess we'll have to keep using our array replication. The replications aren't application consistent are they?

[foggy]

Replicas are also application-consistent provided the corresponding option is enabled in the job settings.

You can use array replication to sync up backup repository to DR site.

[ahahum]

You can use array replication to sync up backup repository to DR site.

That won't work - our backup repository is not on the array (otherwise it wouldn't be a very good backup of data on the array).

Has this feature request had enough interest to make it on the roadmap yet?

[foggy]

Yes, this feature is on our radar.

[ralfl]

Last week I had an issue using MS SQL Log Backup with Backcopy. I've made a support call (Case # 01780001). They told me that it is actually not possible to match the BackupCopy Interval with the Log Backup Interval. If I run daily a VM Backup I should configuire BackupCopy to run single after the VM backup. There is no possibility to copy the SQL Log Backup .vlb to a secure 2nd Place outside our datacenter. A solution can be actually to manually copy the files with a robocopy job.
I've suggest to include this feature as soon as possible to the product....

Another problem I actually see is that during the operation of the base vm backup job sometimes the sql job is reporting errors. I will investigate to this and come back later with this extra.