Comprehensive data protection for all workloads
ccatlett1984
Enthusiast
Posts: 83
Liked: 9 times
Joined: Oct 31, 2013 5:11 pm
Full Name: Chris Catlett
Contact:

Best Practices for SQL Trans log backups - replicate offsite

Post by ccatlett1984 »

I am looking to start running 15min trans log backups on two of my sql servers with veeam (the sql guys are running sql native trans log backups now, and I don't like not knowing if they are successful).

Do I need to do anything special for my copy, and tape jobs to get the trans log backups once I enable it in veeam?
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by veremin »

Since other application is taking care about logs, you will have to use Copy-Only backup mode. Thanks.
ccatlett1984
Enthusiast
Posts: 83
Liked: 9 times
Joined: Oct 31, 2013 5:11 pm
Full Name: Chris Catlett
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by ccatlett1984 »

I should have been more clear.

I want them to stop using the sql native backup, and do the trans log backups with veeam.

I'm getting my ducks in a row, before switching over to veeam for this.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by foggy »

Chris, currently transaction logs files are not processed by backup copy/tape jobs.
ccatlett1984
Enthusiast
Posts: 83
Liked: 9 times
Joined: Oct 31, 2013 5:11 pm
Full Name: Chris Catlett
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by ccatlett1984 »

Well, that sucks.

What about v9?
ccatlett1984
Enthusiast
Posts: 83
Liked: 9 times
Joined: Oct 31, 2013 5:11 pm
Full Name: Chris Catlett
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by ccatlett1984 »

Any other way of protecting those with a copy/tape job?
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by foggy »

You can use file copy/file to tape jobs to copy log backup files to another location. However, keep in mind that they can be applied to the original backup files only (created by the original backup job).
ccatlett1984
Enthusiast
Posts: 83
Liked: 9 times
Joined: Oct 31, 2013 5:11 pm
Full Name: Chris Catlett
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by ccatlett1984 »

Ok, so enabling veeam trans log backups will go to a separate folder on my repository?
I then just point a copy job at that, and my tape job to the copy job?
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by foggy »

Transaction logs backup files are placed into the same folder where corresponding VM backups are stored.
ccatlett1984
Enthusiast
Posts: 83
Liked: 9 times
Joined: Oct 31, 2013 5:11 pm
Full Name: Chris Catlett
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by ccatlett1984 »

So those files are not copied by my vm copy job?

What files do I need to copy?
PTide
Product Manager
Posts: 6408
Liked: 724 times
Joined: May 19, 2015 1:46 pm
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by PTide »

Hi,

Sorry for the late response.

If you want to replay transaction logs some day in future, then you'll have to have your full + incrementals/reverses + .VLBs.
As foggy's already said, the only two ways how you can copy your chain + .VLBs are File To Tape or File Copy Job. The restoration process would be:

- Import Backup Chain from tape/share/whatever place via pointing to your metadata file (.vbm)
- Start Veeam Explorer for Microsoft SQL server
- Choose needed backup and replay logs setting
- start restoration
- wait
- done!

EXAMPLE:

If you need your database to be at the state of January 14th 2007 14:00, then you need to import backup chain that contains restore point of that day, say, January 12th Full + 2 daily incrementals + meta + .VLBs that were created at that day. .VLBs are stored with your backup chain.

Please note, that if you use any forever backup mode (either reverse or forward) then you have to disable "incremental backup" mode in File to Tape Job.

Hope this helps. Don't hesitate to ask if any clarification needed.

Thank you.
mkretzer
Veeam Legend
Posts: 1140
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

[MERGED] Reference Architecture and log backup

Post by mkretzer »

Hello,

I know that there reference architecture states that the local backup should only have a few backup points on fast storage and anything more long term should be via backup copy off-site.

How does that work together with SQL log backup?
Log backups are not copied so we have to have the necessary restore points available on the primary site...

Markus

Gesendet von meinem Nexus 6P mit Tapatalk
mkretzer
Veeam Legend
Posts: 1140
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by mkretzer »

Merging does not answer my question. I know we can copy the files but then the whole managing of the vlbs is all a manual process, which is not acceptable if you have alot of databases.

I am confused because Veeam has the reference architecture but did not build the log backup for it...
PTide
Product Manager
Posts: 6408
Liked: 724 times
Joined: May 19, 2015 1:46 pm
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by PTide »

Hi,

If you want to make sure that in case of a total disaster (all primary backups are lost) at primary site you'll be able to recover your SQL server with a minimal data loss then I think you should consider using replication job for critical servers. Backup copy jobs are intended to keep an additional copy of backups offsite for a long term retention of VM and were not designed to perform SQL granular restores. I understand your confusion and consider your question as a feature request.

Thanks
mkretzer
Veeam Legend
Posts: 1140
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by mkretzer »

Hello PTide,

that is not our problem.

The problem is that we cannot implement the reference architecture because we need to be able to restore log backups for the last 30 days.
But we need to have faster storage on the primary site (als in the reference architecture) which means we have to pay alot more for fast storage which can still hold the backups of 30 days.

It basically comes down to this question: Will 9.5 allow backup copy of DB logs?

Markus
PTide
Product Manager
Posts: 6408
Liked: 724 times
Joined: May 19, 2015 1:46 pm
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by PTide »

Will 9.5 allow backup copy of DB logs?
No, unfortunately, it won't.
pkelly_sts
Veteran
Posts: 600
Liked: 66 times
Joined: Jun 13, 2013 10:08 am
Full Name: Paul Kelly
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by pkelly_sts »

Ouch!
Having literally just this weekend completed migrating away from a SQL Cluster to a standalone SQL primarily to bring the SQL server into the scope of Veeam backups (simply because at the end of the day, an RTO of up to 15 minutes rather than seconds was actually sufficient for their needs), and getting ready to show them (the users) how amazing the restore options are as well as the far improved off-site copies will be (in comparison to their daily full DB backups which then went to tape which then went off-site some time the next day), I'm rather shocked to discover that off-site copies of TL backups can't (EASILY) be brought into scope as well without a load of workarounds!

:-/
aspen432
Novice
Posts: 6
Liked: never
Joined: Dec 23, 2014 2:13 pm
Full Name: Max Speyer
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by aspen432 »

Am just stumbling upon this thread and have to agree that this is a real bummer. Would really be a nice feature to have for us so I'm hoping it's under serious consideration for a future release.
mkretzer
Veeam Legend
Posts: 1140
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by mkretzer »

I also hope this gets "fixed" in 10.0!
mrapp
Lurker
Posts: 1
Liked: never
Joined: Mar 09, 2017 2:03 pm
Full Name: Mitchell Rapp
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by mrapp »

Is this T-Log restriction only for replicated SQL backup copies or all as SQL PIT log-based restores seem to work from the original copy quite well.
kubimike
Veteran
Posts: 373
Liked: 41 times
Joined: Feb 03, 2017 2:34 pm
Full Name: MikeO
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by kubimike »

+1 this doesn't make 3-2-1 easy for SQL!
Samba222
Service Provider
Posts: 79
Liked: 24 times
Joined: Jan 18, 2017 11:54 am
Full Name: Ronald
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by Samba222 »

oh yes, we also have the wish that transaction logs are also included (or the possibility) in Backup to Tape Job - this would be a really nice feature :-)
jvlad
Influencer
Posts: 10
Liked: 1 time
Joined: Jul 10, 2018 6:20 pm
Full Name: Jason
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by jvlad »

The discovery of this transaction log backup-copy shortcoming has been an unsettling find of Veeam B&R 9.5. There should be a warning popup at a minimum when you setup the TL backups so atleast backup administrators are aware of this limitation if a BackupCopy will be put in place.
Seriously the DB Server backup is 95% there but Veeam decided to release the product without a viable built in and supported option to replay logs at a DR site.
Feature consistence in this product seems to be lacking. Found another post where someone stated this issue should be considered more of a bug than a feature request and frankly I agree with him and Veeam development should be all over it. Its now July 2018 and still no option for this and the earliest post I found on this was Jan 2015, and yet this feature is still in the works. Ohhh not good.

https://forums.veeam.com/veeam-backup-r ... 25349.html

Love the product but just a bit bummed out I guess.
nrexpress
Service Provider
Posts: 3
Liked: never
Joined: Mar 29, 2018 6:27 pm
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by nrexpress »

I also echo that the lack of a transaction log backup copy to be a significant issue. The customers that I know that want to use Veeam have multiple reasons for doing so -- but generally because 1) they need a DR solution, not just a backup solution and 2) they need to backup systems, not just files, and therefore are generally looking to backup SQL. These same customers generally have at least one SQL database that are too big to send both full backups and full replicas over the same WAN link.

Is there an update on where we are on this feature request?
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by foggy » 1 person likes this post

It is on our short-term list.
alesovodvojce
Enthusiast
Posts: 61
Liked: 9 times
Joined: Nov 29, 2016 10:09 pm
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by alesovodvojce »

We have assured the board that the critical system is having 15minute RPO and today after accidently finding out that our Backup copy job seems to ignore that log transfers I ended in this forum thread. It is embarassing that the backup is not doing something really expected.
Thanks for looking into that!
nrexpress
Service Provider
Posts: 3
Liked: never
Joined: Mar 29, 2018 6:27 pm
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by nrexpress »

Is there an update on the feature request for the SQL transaction log being sent over backup copy job?

Thanks.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Best Practices for SQL Trans log backups - replicate off

Post by foggy »

No changes since my last post month ago.
dgaccu
Novice
Posts: 9
Liked: never
Joined: Dec 18, 2014 5:46 pm
Full Name: Danny Williams

Re: Best Practices for SQL Trans log backups - replicate offsite

Post by dgaccu »

Since we're about a year from the last post here, foggy could you update us on the progress since it is on the short term list?
jvlad
Influencer
Posts: 10
Liked: 1 time
Joined: Jul 10, 2018 6:20 pm
Full Name: Jason
Contact:

Re: Best Practices for SQL Trans log backups - replicate offsite

Post by jvlad »

This post is from 2015... I have big doubts this request is even in the early states of an Alpha built. Hell I have my doubts this request is even a scribble on a white board somewhere.
Post Reply

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 227 guests