SQL backup and Log file truncate advice please.

[deaton]

Hello,

Using Veeam 9. Backing up a Windows 2012R2 Server running MS SQL 2014 Web, which is a Hyper V VM.

I've been backing up MS SQL servers since Veeam v7, but I only do full backups and have some SQL jobs that backup the DBs and truncate logs etc. as the DBs are all small.
In a few months I will have to backup a server with a database of 85GB, so in my head I think the best thing to do is use Veeam to do nightly server backup files and truncate the SQL logs.
I've been testing this process, but I'm confused as to what Veeam and MS SQL will do once the backup has been done.

Once Veeam has backed up the server and truncated the log file (which does change size initially), any updates to the database are made to the database file and not the log file. So the log file never grows.
The only way SQL will write to the log file again is if I do a MS SQL backup from Management Studio.

Am I approaching this all wrong?

Thanks,

David

[PTide]

Hi and welcome to the community!

so in my head I think the best thing to do is use Veeam to do nightly server backup files and truncate the SQL logs.

You can setup a nightly incremental backup + enable log processing every X minutes. That will allow you to perform point-in-time recovery of your database server without need to perform VM backup every X minutes. It's recommended to perform periodic full VM backup, for example you can setup a job with VM nighlty incremental backup + every X minutes transaction log backup + VM Full backup every last day of the month.

So the log file never grows. The only way SQL will write to the log file again is if I do a MS SQL backup from Management Studio.

That's only partially true. You can't prevent MSSQL from writing to log however you can prevent log file from extensive growth - please see the article.

Thank you.

[deaton]

Thanks very much. I've been playing about and I think I'll be able to sort something out
Veeam sent me some MSSQL & Veeam video links as well.