Veeam backups and Transaction Log truncation

• UgBug

  SSC Enthusiast

  Points: 189

  More actions

  April 2, 2020 at 8:23 pm

  #3740094

  The hospital that I work for has started to use Veeam for backing up our physical and virtual servers, including SQL Server.  I have years of SQL developer/architect experience, but am relatively new in the DBA role.

  We are using a mix of SQL Server 2012-2017.

  Veeam's setting for backing up transaction logs (for a database in FULL recovery model) is to TRUNCATE the log with each 'backup'.

  DB's are in FULL recovery model; DB full back up daily, T-Logs backed up hourly

  Have a few questions:

  1. Anyone have experience performing a point in time recovery of a DB from Veeam?  Any advice, take aways, issues?
  2. Anyone have an experience with Veeam and transaction log truncating?  All my reading and experience says that performing a TRUNCATE on a transaction log is not something that should be done frequently and I am a bit worried that truncating after every backup could cause problems.  Veeam's documentation is not fantastic, but some of what I read sort of, kind of, can imply that backing up in this fashion could cause a break in the LSN chain.

  Looking for any advice or experiences that anyone is willing to share.

• April 2, 2020 at 8:33 pm

  This was removed by the editor as SPAM

• Jeffrey Williams

  SSC Guru

  Points: 90252

  More actions

  April 2, 2020 at 8:45 pm

  #3740096

  The TRUNCATE_ONLY option of the backup log command is no longer available.  If that command is executed against a database instance greater than 2008 it will fail with a statement that TRUNCATE_ONLY is not a valid backup option.

  If Veeam is performing a truncate - then the only way that could occur would be to switch the recovery model to simple - then back to full.  That would eliminate any value of having a database in the full or bulk-logged recovery models since you could never perform a point in time recovery of that database.

  The other option would be that Veeam is actually running the transaction log backup - but writing the results to the null device.  Essentially throwing away the transaction log backup.

  Veeam does have a setting to perform transaction log backups and how often to perform them.  I would recommend making sure that is set appropriately and the system is actually backing up the transaction log and shipping it to the repository.  These should show up in the repository as .VLB files.

   

   

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• Jo Pattyn

  SSC-Dedicated

  Points: 32069

  More actions

  April 3, 2020 at 9:45 am

  #3740167

  We are also using Veeam to do our SQL backups.

  1. It is possible to restore to point in time. Test it. We originally had some problems with timeouts on restoring oracle backups.

  2. Truncate option: we have it set at "backup logs periodically" every 15 minutes so we can do point in time restores through Veeam. It breaks the backupchain outside of Veeam. https://helpcenter.veeam.com/docs/backup/vsphere/transaction_truncation.html?ver=100