In Veeam version 8 they released a new feature where you can schedule a job to periodically backup SQL transaction logs. If you read the help files there is very little information about how to set this up or how it works. I had to look into this feature for a customers requirement so thought I would document the process of how to set it up and how to restore from them.
First of all you need to create a SQL Veeam backup job and ensure that the guest processing user has the necessary SQL permissions.
Then you go into the job and into the guest processing option. Click on Applications under application aware processing.
Then click on the edit button.
Click on the SQL tab
Select backup logs periodically and set your desired timings. Once done ok out of the job back to the main Veeam backup shell.
You can now kick off the job or wait for the next schedule. When it runs for the first time, run manually or by schedule, it will carry out a normal veeam backup, where it snapshots the VM. Under running jobs we will see 2 jobs. One is for the normal Veeam backup and a subsequent job is queued for the SQL logs.
Looking at the queued SQL log backup we can see that its waiting for a SQL VM to appear.
Once the backup job completes ok it will truncate the transaction logs. You will not see any LDF’s shrink in size if you do not have any autoshrink parameters set. The transaction log job will now start and back them up to the specified repository.
This backup does not snapshot the VM at the VMware level and just uses the in built SQL functions to access them. It will not back up any transaction logs for a database if the recovery model is set to simple.
Once the transaction log job is complete, the job will pause and wait the desired backup interval. If you branch out the ready backups for this VM we can now see the main backup followed by the transaction log backups that are available.
You can now do point in time recovery’s on this database. I find the easiest way to initiate this is to right click on the VM in the ready backup list and then select “Restore Application item”, “Microsoft SQL Server databases”
Then select restore from the latest available backup. Click next, next, finish.
Once you click on finish the SQL Browser will appear. If we click on a database then you can see on the right hand side the time of the available restore points. Notice the end time is the point of the last log transaction backup. NOT the time of the last full VM backup.
You can now right click on the database and select “Restore point in time state to Database Name”
You can now click restore to a point in time and drag the slider to the time you require.
Veeam also make a nifty program called Veeam Explorer for SQL. With this program you can quickly perform point-of-time restores of your SQL databases.
Thats about it. All good 🙂