SQL backup and Log file truncate advice please.

Hyper-V specific discussions

SQL backup and Log file truncate advice please.

Veeam Logoby deaton » Wed Feb 10, 2016 3:51 pm

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

Re: SQL backup and Log file truncate advice please.

Veeam Logoby PTide » Thu Feb 11, 2016 11:40 am

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

Re: SQL backup and Log file truncate advice please.

Veeam Logoby deaton » Thu Feb 11, 2016 3:11 pm

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


Return to Microsoft Hyper-V



Who is online

Users browsing this forum: No registered users and 10 guests