Best Practices for SQL Trans log backups - replicate offsite

Availability for the Always-On Enterprise

Best Practices for SQL Trans log backups - replicate offsite

Veeam Logoby ccatlett1984 » Thu Aug 20, 2015 10:06 pm

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?
ccatlett1984
Enthusiast
 
Posts: 83
Liked: 9 times
Joined: Thu Oct 31, 2013 5:11 pm
Full Name: Chris Catlett

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

Veeam Logoby v.Eremin » Fri Aug 21, 2015 10:59 am

Since other application is taking care about logs, you will have to use Copy-Only backup mode. Thanks.
v.Eremin
Veeam Software
 
Posts: 12580
Liked: 906 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

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

Veeam Logoby ccatlett1984 » Fri Aug 21, 2015 12:55 pm

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.
ccatlett1984
Enthusiast
 
Posts: 83
Liked: 9 times
Joined: Thu Oct 31, 2013 5:11 pm
Full Name: Chris Catlett

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

Veeam Logoby foggy » Fri Aug 21, 2015 12:57 pm

Chris, currently transaction logs files are not processed by backup copy/tape jobs.
foggy
Veeam Software
 
Posts: 14086
Liked: 1027 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

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

Veeam Logoby ccatlett1984 » Fri Aug 21, 2015 2:13 pm

Well, that sucks.

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

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

Veeam Logoby ccatlett1984 » Fri Aug 21, 2015 2:16 pm

Any other way of protecting those with a copy/tape job?
ccatlett1984
Enthusiast
 
Posts: 83
Liked: 9 times
Joined: Thu Oct 31, 2013 5:11 pm
Full Name: Chris Catlett

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

Veeam Logoby foggy » Fri Aug 21, 2015 3:00 pm

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

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

Veeam Logoby ccatlett1984 » Fri Aug 21, 2015 3:55 pm

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?
ccatlett1984
Enthusiast
 
Posts: 83
Liked: 9 times
Joined: Thu Oct 31, 2013 5:11 pm
Full Name: Chris Catlett

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

Veeam Logoby foggy » Fri Aug 21, 2015 3:58 pm

Transaction logs backup files are placed into the same folder where corresponding VM backups are stored.
foggy
Veeam Software
 
Posts: 14086
Liked: 1027 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson

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

Veeam Logoby ccatlett1984 » Fri Aug 21, 2015 4:13 pm

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

What files do I need to copy?
ccatlett1984
Enthusiast
 
Posts: 83
Liked: 9 times
Joined: Thu Oct 31, 2013 5:11 pm
Full Name: Chris Catlett

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

Veeam Logoby PTide » Mon Aug 24, 2015 5:07 pm

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.
PTide
Veeam Software
 
Posts: 2846
Liked: 231 times
Joined: Tue May 19, 2015 1:46 pm

[MERGED] Reference Architecture and log backup

Veeam Logoby mkretzer » Tue Jun 07, 2016 2:02 pm

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
Expert
 
Posts: 236
Liked: 54 times
Joined: Thu Dec 17, 2015 7:17 am

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

Veeam Logoby mkretzer » Wed Jun 08, 2016 7:18 am

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...
mkretzer
Expert
 
Posts: 236
Liked: 54 times
Joined: Thu Dec 17, 2015 7:17 am

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

Veeam Logoby PTide » Wed Jun 08, 2016 12:38 pm

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
PTide
Veeam Software
 
Posts: 2846
Liked: 231 times
Joined: Tue May 19, 2015 1:46 pm

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

Veeam Logoby mkretzer » Wed Jun 08, 2016 1:22 pm

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
mkretzer
Expert
 
Posts: 236
Liked: 54 times
Joined: Thu Dec 17, 2015 7:17 am

Next

Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Bing [Bot], pndscm667 and 33 guests