Transaction Logs in Backup Copy or Replica

[veremin]

The said functionality has been requested before. So, your voice for adding it has been counted. Thanks for the feedback.

[ahahum]

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.

Foggy - what would be the most efficient backup method to use for copying this to a replicated volume on our array? Current setup is just a physical box with large drives for our primary backup repository. I was considering creating a volume on the array that would be replicated to DR facility and copying the backup folder for this one VM to that repository. I am curious which would be the most efficient from both a total consumed storage standpoint as well as least amount of changed blocks.

I've read through this and it is very helpful, but I'm not sure which of these options may be the best benefit for me trying to handle this. Primary objective would be to simply get the SQL log backups offsite along with the necessary files to restore them. We wouldn't need to keep as many on the replicated volume as we have on our local repository so I'd probably copy last 48h of files or something similar. I understand that this may also pose a potential hangup depending on which backup method I choose for the VM.

Your expertise and advice is appreciated.

[ahahum]

Missing link reference in my post above.
http://www.virtualtothecore.com/en/veea ... torage-io/

[foggy]

Amount of changed blocks does not depend on the backup method and is similar for all of them. The most space efficient methods are reversed incremental and forever forward.

[Bunce]

Guys,

Why not do the following:

1 - Use the native SQL backup functionality to backup the T-Logs to a volume on the VM itself. Set a scheduled job to clean out old backups according to your RPO's.
2 - Use Veeam just to backup/replicate/copy the VM (including the volume containing the T-Logs) - remember to disable log truncation in Veeam

We messed around with the earlier SQL backup stuff in Veeam earlier but found it unreliable (haven't gone to V9 yet).

The above has always worked for us and provides:
- Transactionally consistent backups (VSS is still used by Veeam to correctly Quiesce)
- An option to restore to the most recent Veeam (VM) backup if that's all that's needed
- A further option to then restore to a Point-In-Time using -TLog backups to meet an RTO objectives.

I'm sure the Veeam functionality in V9 is promising but for something this critical we much prefer to use the inbuilt SQL tools for PIT restores which will always be more reliable - and the additional time taken for the PIT restore is negligible.

[nengelberth]

1 - If you're backing up T-Logs to the volume on the VM itself, you're not going to meet any RPO shorter than 1 day, because your T-Log backups are going to be *gone* when the machine fails.
2 - Veeam's TLog backups work fairly well for us on an hourly cycle (though they occasionally overrun and miss an hour's cycle, largely because we have fairly low quality storage for our backup repository and it gets overwhelmed).
3 - The goal is to have a single interface for all of the backups and restores, so I don't have to train staff in two different ways to backup/recover (or have a dedicated staff member on call just for DB server recoveries).

Aside from us not really being covered if the server room catches fire, we're fairly happy with the process of how Veeam SQL backups work -- just used them this past weekend, actually, and the restore process went well. We're just trying to get PIT recovery from our off-site backups without needing to spend tens of thousands of dollars on Data Domain level storage and a high bandwidth MPLS link.

[kallen2323]

I am using Veeam Backup and Replication version 9 update 1 and I have been trying to get 2 local repositories to be identical by using the backup copy job feature. The backup copy job doesn't add/update point-in-time restore points for SQL that are part of the regular backup job I use. I find this misleading because the software doesn't prompt or notify me in any way. If I hadn't tried to perform a point-in-time test restore for SQL using the backup copy data, I would have never known the .vlb data is not included. I would like to ask that point-in-time restore points be added to the backup copy process so I can have a true copy of my backups.

[EvanWaite]

I'd also like to add our voice to this feature. We're using one of the more common architectures with multiple fast primary backup repositories holding 3 restore points and backup copy jobs sending data to onsite and offsite dedupe appliances. If the log backups aren't copied over, it means we have only ~3 days where we can do a point in time recovery. It also means if we lose the primary copy (host or datacenter failure), we have up to 24hrs of missing data.

Proposed methods of using File Copy jobs aren't practical with Per-VM chains and Scale-Out repos in v9. They also don't give us a solution to change compression levels so terrible with dedupe secondaries.

We're hoping this feature will make it into v9.5 (for MSSQL at least, Oracle would also be nice) as it will finally allow us to stop using TSM on top of Veeam. The only mention of this missing feature in the docs is under the Backup-to-tape section (https://helpcenter.veeam.com/backup/vsp ... begin.html) so it's possible many don't know their logs aren't moving over (I'll admit, we just assumed they did).

[mbroaders]

Is there any way to have the SQL Transaction Log backup files copied within the Backup Copy Job? If not, what would be the best way to ensure that SQL transaction log backup files are kept offsite?

[JoshuaPostSAMC]

I'm planning on setting up Backup Copy Jobs later this week. You mean SQL Transaction logs aren't copied over as part of that? I don't understand why that would be?

[Gostev]

Backup Copy jobs do not currently process log backups, just VM backups.

[rasmusan]

Hello


Today backup copy jobs does not process transactions log backups (SQL etc.) - I receive this request from customers quite often, and customers try to create custom solutions using scripts etc. to manually do the copy...

However it would be great if this was built into backup copy jobs - are there any plans to include this functionality in upcoming v10 ?


/Rasmus

[foggy]

Hi Rasmus, as you can see, this has been already requested a number of times, so we have this in mind.

[kubimike]

Hi all, so I need a backup copy job that not only copies the data for the VM but need the ability to bring the transaction logs along too. I read the work around for a file copy or file to tape and that won't cut it for me in this case. If I create a file copy job and point it to the folder where the SQL backup resides it has no way to selectively pick the latest backup. It will just copy the entire folder, totally useless for me not to mention I don't have that much room on my other repo. The only way I could think of doing this is from DOS using forfiles, having it search by yesterdays date and copy the files (Chain + VLBs) elsewhere.

[kubimike]

**Updated edit, can I still use the backup copy job on the SQL server and just copy the VLBs + metadata to another temporary folder with a dos script and use a tape copy job or file copy job to save everything manually copied ? Will this still all work when it comes time to do a SQL recovery? This way lets say for example I want to restore 3 days back I'll need the chain + VLBs + metadata copy them to a temporary location, import the backup then do my SQL restore. Thanks!

[Mike Resseler]

Hi Mike,
Your request from making sure that a BCJ can do this is noted. You are not the first to ask so consider your vote counted towards the importance of the request

For your second post: I am sure nobody has ever tested it, and something in my mind tells me it is not going to work. If you do a BCJ then that stored data will be different from the backup data and I doubt that you can import that BCJ backup, together with the chain and VLBs and it will be recognized and usable.

[kubimike]

Hi Mike, well not according to this veeam-backup-replication-f2/best-practi ... ml#p159044

[jameskilbynet]

This would be a huge win and shouldn't be too difficult to implement

[ian0x0r]

Yup, please add my voice to this request. I'm surprised that it doesn't do this already.

[FrancWest]

Too bad this critical feature still isn’t implemented. We ended up installing the Veeam Windows agent inside our SQL server VM. The backup job for this VM runs every hour and also runs a transaction log backup every 15 minutes. We also have a backup copy job that runs every hour. So in case of complete disaster (primary site lost) we loose 1 hour of data, but with a minor disaster we are able to use the transaction log backups if the primary site is still intact. We couldn’t use a regular vm backup job during production hours because the VMware snapshot creation causes a freeze of the Sql server 3 seconds which affects clients using the databases.

Franc.

[yakamoneye18]

Hello everyone,
we are currently switching our MS SQL Databases to transaction logs an backup them with Veeam B&R. We also use tape backups, one daily job (SQL and Fileserver) and one monthly job.
Tape is working great so far, now we want to add the Transaction Log backup files (vlb) into the daily tape job.

- Is this even supported? I already read that copy job backups cannot be used to do point-in-time restore of SQL DBs, because it creates completly new restore points. Since the backup rhythm of SQL backup and SQL tape job is the same, can this work here?
- if it works: what is the best way to achieve this? I tried File-to-Tape job, but i always get errors since the most recent VBL file is still locked by the Transaction Log Copy job.

Thanks!

Tobias

[PTide]

Hi,

- no, not only it is not supported, but also such scenario hasn't been tested. Theoretically it may work if you copy every single vlb and make sure that you apply the appropriate piece of the sequence of vlbs to the chain that you have at hand at the moment of restore.

- have you tried scheduling your File-to-Tape to run a few minutes after the transaction log copy job has finished?

Thanks

[yakamoneye18]

Hi PTide,
thanks for your answer. Changing the Schedule to the point where the transaction log file is closed and the job is finished was one thing i wanted to test. But if i cannot even use these transaction logs with the backups on the tape, i think this is useless.

Are there any plans on the side of Veeam to support moving backups to offline locations together with their transaction log backups? In times of crypto trojans and also with regard to the 3-2-1 model of backups recommended by Veeam, this would be a great feature.

[foggy]

Are there any plans on the side of Veeam to support moving backups to offline locations together with their transaction log backups?

Yes, we do plan to support logs replication within backup copy jobs at least.

[Tos]

Hi

Our customer have question.
They run backup Oracle VM and transaction logs.
https://helpcenter.veeam.com/docs/backu ... tml?ver=95

They want to copy these data to DR site storage.
We think we can copy backup data to DR site storage by using backup copy job.
But, we think transaction logs can't be copied to DR site storage.

Do you know the workaround ?

Regards
Toshi

[Dima P.]

Hello Toshi.

Backup copy job does not process transaction log backup files. From the top of my head you could use robocopy with a scripts to periodically copy the backup files together with transaction log backup files to a secondary location. Hope it helps. Cheers!

[Tos]

Hi
Thank you, ! I will try it.

[sandsturm]

Are there any plans on the side of Veeam to support moving backups to offline locations together with their transaction log backups?

Yes, we do plan to support logs replication within backup copy jobs at least.

My question is: Do you have a roadmap, when this feature will come GA, whit which VBR version?

thx,
sandsturm

[veremin]

Unfortunately, we cannot share ETA for future releases. Thanks!

[Gostev]

I just wanted to confirm that enabling backup copy jobs to copy transaction logs along with actual image-level backups is very high on our priorities list. And I believe most of the engine work was actually done a while ago already, so now we just need to ship it at the next window of opportunity... unfortunately, the required dev and QC teams have been extremely busy with another very major upcoming feature.