Host-based backup of Microsoft Hyper-V VMs.
Post Reply
deaton
Lurker
Posts: 2
Liked: never
Joined: Mar 28, 2013 11:48 am
Full Name: David Eaton
Contact:

SQL backup and Log file truncate advice please.

Post by 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
Product Manager
Posts: 6408
Liked: 724 times
Joined: May 19, 2015 1:46 pm
Contact:

Re: SQL backup and Log file truncate advice please.

Post by 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
Lurker
Posts: 2
Liked: never
Joined: Mar 28, 2013 11:48 am
Full Name: David Eaton
Contact:

Re: SQL backup and Log file truncate advice please.

Post by 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.
Post Reply

Who is online

Users browsing this forum: No registered users and 20 guests