PowerShell restore job for SQL server

[michawel]

Hi, I found interesting behavior. We have Veeam Backup 13 (appliance) with VHR. We are backing up Always-On SQL cluster. Everything works. We are able to backup in all possible modes (also using application backup - however in this case not able to restore to standalone server - but based on technical support is this by design). We are backing up physical SQL server. We would like to restore using Powershell script. This is pretty common. But here I found some issue. When I backup this server without Application-aware backup or with Application aware backup enabled with copy-only mode I am not able to restore database to different server. When I enable full transactional log backup I am able to restore everything properly. However when I have done this over console so I run backup without application aware and then tried to restore I was able to restore database without issue. SQL Explorer opened properly, database found, database is possible to restore. But using Powershell I am not able to find the object.
I hope that I am using proper sniplets. Problem is in command Get-VBRApplicatationRestorePoint -SQL which is not able to find correct restore point. Normally Get-VBRRestorePoint is used however this cannot be used for restore of database. Is there any other way? I am just asking we are able to do what we want but this is interesting inconsistency.
Just short summary: Get-VBRApplicationrestorePoint is not returning recovery point when Application-aware backup is disabled (this makes sense) however also in case App-aware backup is enabled and only Copy-log function is set and this can be weird little bit.

[david.domask]

Hi Michal,

Enabling Application Aware Processing (AAIP) has two features:

- Ensure Application Consistent backups of your critical workloads
- Collect application metadata for the Veeam Explorers to "know" that the backup was done with AAIP

Get-VBRApplicationRestorePoint expects that this metadata is present for the workflow -- within the console, some magic can be done to manually mount backups without the AAIP information.

Can I ask, is there a reason you want to perform the backups without AAIP? Or this is more of an interesting curiosity from your side? Regardless, using AAIP especially for an SQL server is strongly recommended.

[michawel]

I absolutely agree with you. Some magic is done because it is possible to use console features. If it is reason - yes it is - we are migrating from different SQL Backup/Restore solution where logs are completely managed. So we need to temporary use this method using PS script. However as I mentioned in basic description we know how to solve it but question/article is related to little unexpected behavior in Powershell vs. Console. Final state of this task will be fully application-aware backup with the backup of the logs task and this will work without troubles (already verified).
I am just asking if this is expected behavior and how the console is able to do this. Btw this is not only one not so logical thing related to SQL. Second one (already mentioned in original article) is problem with inability to restore SQL DBs in Always-on in Application-mode backup to standalone server. (this was confirmed by Veeam support).

[david.domask]

Got it, glad we're on the same page regarding AAIP for SQL servers.

I don't recall off the top of my head how it's handled with the console, but for PowerShell it's simply that the entry point for starting the application recovery session requires that additional metadata for the Get-VBRApplicationRestorePoint to even know that a restore point is valid -- I _think_ that with the console when initiating the SQL Explorer for restores, there is some logic during the backup mounting to try and detect applications, though sometimes this logic is not successful and you will receive an warning in the restore wizard.

With an automated script, there isn't good way to give feedback to the user besides failing the session, but again for PowerShell to even start the SQL restore process, it must first get an object returned from Get-VBRApplicationRestorePoint by design. So while I understand there is a bit of discrepancy, the workflow for PowerShell differs in that a specific object needs to be present and retrieved with PowerShell before mounting the backup for the SQL Explorer cmdlets can even be used.