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).