Restore-DbaDatabase with -Continue when Database has Memory Optimized File Group and/or multiple differential_base_lsn

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

WARNING: [13:22:36][Select-DbaBackupInformation] Failed to find LSN or RecoveryForkID for WARNING: [13:22:37][Restore-DbaDatabase] Failure | Microsoft.Data.SqlClient.SqlError: The log in this backup set terminates at LSN 877107000062444000001, which is too early to apply to the database. A more recent log backup that includes LSN 877139000028936800001 can be restored.

writeErrorStream      : True
PSMessageDetails      : 
Exception             : System.Exception: Microsoft.Data.SqlClient.SqlError: The log in this backup set terminates at LSN 877107000062444000001, which is too early to apply to the database. A more recent log backup 
                        that includes LSN 877139000028936800001 can be restored. ---> System.Exception: Microsoft.Data.SqlClient.SqlError: The log in this backup set terminates at LSN 877107000062444000001, which is 
                        too early to apply to the database. A more recent log backup that includes LSN 877139000028936800001 can be restored. ---> System.Management.Automation.MethodInvocationException: Exception 
                        calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'SQLP-130E-P-13.odnss.com'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 
                        'SQLP-130E-P-13.odnss.com'.  ---> Microsoft.SqlServer.Management.Smo.SmoException: Microsoft.Data.SqlClient.SqlError: The log in this backup set terminates at LSN 877107000062444000001, which is 
                        too early to apply to the database. A more recent log backup that includes LSN 877139000028936800001 can be restored.
                           at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages, Boolean 
                        retry)
                           at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
                           at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
                           --- End of inner exception stack trace ---
                           at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
                           at CallSite.Target(Closure , CallSite , Object , Object )
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
                           at lambda_method(Closure , Object[] , StrongBox`1[] , InterpretedFrame )
                           --- End of inner exception stack trace ---
                           --- End of inner exception stack trace ---
TargetObject          : SQLP-130E-P-13
CategoryInfo          : NotSpecified: (SQLP-130E-P-13:PSObject) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Restore-DbaDatabase,Stop-Function
ErrorDetails          : Microsoft.Data.SqlClient.SqlError: The log in this backup set terminates at LSN 877107000062444000001, which is too early to apply to the database. A more recent log backup that includes LSN 
                        877139000028936800001 can be restored.
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Stop-Function, <No file>: line 97978
                        at Restore-DbaDatabase<End>, <No file>: line 71360
                        at <ScriptBlock>, C:\Users\DDrake.A\Desktop\Restore-IntelliStatDB.ps1: line 168
PipelineIterationInfo : {0, 1}

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference
Get-DbaAgBackupHistory -SqlInstance '<AGListener>' -AvailabilityGroup '<AGName>' -Database '<DBName>' -Last |
    Restore-DbaDatabase -SqlInstance '<DestServer>' -DatabaseName '<DestDBName>' -NoRecovery -FileMapping <FileMap> -TrustDbBackupHistory -MaxTransferSize 4194304 -BufferCount 1024

<Wait until some more log backups run>

Get-DbaAgBackupHistory -SqlInstance '<AGListener>' -AvailabilityGroup '<AGName>' -Database '<DBName>' -Last |
    Restore-DbaDatabase -SqlInstance '<DestServer>' -DatabaseName '<DestDBName>' -NoRecovery -Continue -FileMapping <FileMap> -TrustDbBackupHistory -MaxTransferSize 4194304 -BufferCount 1024

Please confirm that you are running the most recent version of dbatools

2.1.16

Other details or mentions

I'm not sure how easy this is to reproduct, but my script was working perfectly fine for a week or so, but then started bombing. Upon deeper investigation, it appears to be related to when Get-RestoreContinuableDatabase returns multiple rows for the same database. At least in my case, the 2 distinct rows were due to the Memory Optimized filegroup having a different differential_base_lsn than all the other data files. I have been able to work around the issue by taking the same SQL query in that cmdlet, and wrapping differential_base_lsn and redo_start_lsn in MIN() and using GROUP BY vs DISTINCT (honestly not sure that's the proper fix, but seems to work so far), then passing that to Select-DbaBackupInformation; Format-DbaBackupInformation; Test-DbaBackupInformation; and finally Invoke-DbaAdvancedRestore.

SQL used for Continue Points:

SELECT
    [Database]                  =       db_name(database_id),
    [differential_base_lsn]     =       MIN(differential_base_lsn),
    [redo_start_lsn]            =       MIN(redo_start_lsn),
    [FirstRecoveryForkID]       =       redo_start_fork_guid
FROM
    master.sys.master_files
WHERE
    redo_start_lsn      IS NOT      NULL
GROUP BY
    database_id,
    redo_start_fork_guid;

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe), Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Name Value

PSVersion 5.1.14393.6343
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.6343
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Version for both Source & Destination:

Microsoft SQL Server 2016 (SP3-GDR) (KB5029186) - 13.0.6435.1 (X64) Jul 30 2023 19:53:42 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.8.4729.0

PSChildName Version

v2.0.50727 2.0.50727.4927 v3.0 3.0.30729.4926 Windows Communication Foundation 3.0.4506.4926 Windows Presentation Foundation 3.0.6920.4902 v3.5 3.5.30729.4926 Client 4.8.03761 Full 4.8.03761 Client 4.0.0.0

dataplat / dbatools