SQL Backups

[jarchibald]

I am trying to get a reporting server working. this is a physical (not a VM) machine that is running SQL and has a read only copy of some databases from our production VM machine.
This is for the CFO and others to hammer away on when creating reports.

I was working with RedGate SQL Backup Pro where I was log shipping the database to the server. Application Aware processing in Veeam was turned off.
This worked, but I ran into issues that the database isn't flagged as backed up and the logs are truncated growing outrageously.
I added weekly backup and daily diff backups and that kinda solved the issue, except I wasn't doing every database on the server and I had to have a local storage drive for all the backups.

So I looked back at Veeam for the solution. I disabled all the above and turned on Veeam's app-aware processing and log shipping. I was hoping to find the log files that I could restore to the reporting server.
The best that I have been able to do now is to run a restore job in the GUI, select the current backup and restore the single database to the other server. It worked, but how do I automate this to run daily?
I have found the powershell commands for veeam and looking for help on how to get the current restore point on a backup job/vm and restore it to a secondary server.
Or a better suggestion on how to solve this problem.

This is my current thoughts:
$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name "ProductionServerName"
$session = Start-VESQLRestoreSession -RestorePoint $restorepoint[0]
$databasenames = @("data1", "data2")
$db1 = Get-VESQLDatabase -Session $session[0] -Name @databasenames

Restore-VESQLDatabase -Database $db1 -ServerName "reportsvr" -TargetFolder "S:\SQLDATA\" -Force true -RecoveryState Standby

thanks

[PetrM]

Hi Joe and Welcome to Veeam R&D Forums!

In general, PowerShell is a suitable choice for automation scenarios. I move this topic to the PowerShell forum since the question primarily pertains to automation workflow, rather than our application-specific integrations.

We should think about better options only if we comprehend the problems associated with the current idea, but I don't see any issues with the script above. Have you attempted to run the script and encountered any challenges?

Thanks!

[jarchibald]

I haven't attempted to run the script yet. I didn't know if the logic was sound on it.

I did just discover the sql plugin that I am giving a test on.

[PetrM]

Hi Joe,

Sure, SQL plug-in would also work and you have an option to automate the CLI restore command execution, for example with SQL Agent jobs.

Thanks!

[jarchibald]

Well, I hit a snag.
I ran a full backup of the database and then a diff backup and created a SQL Agent job.
edited the job to create a schedule.
logged into the reporting server and used the plugin to create a restore job that I copied to powershell and did some modifications to and ran the job in the wizard to run the first restore.
Worked well.
Created the SQL Agent job on the reporting server to automate the job.
I now get an error that the restore failed due to the backup is out of sync.
This is probably due to the veeam backup of the vm of the server.

Any thoughts on how to get around this? Short of running a restore on the full backup, the production database is 250Gb and takes awhile to backup/restore.

[PetrM]

It's difficult to find out the root cause of the error without analyzing debug logs, you may open a support case and ask our engineers to have a look at the issue. Please don't forget to share a support case ID with us. Also, you may try to rescan repository to update the information about backups in Veeam configuration database and repeat the test.

Thanks!