Dbatools: Restore-DbaDatabase with STOPAT ($RestoreTime) picks up incorrect set of files for Restores

[amon09]

Steps to Reproduce

Hypothetical backup setup:

• Every Saturday - 5 PM: Full backups
• Sunday - Friday - 5 PM: Differential backups
• Every 10 minutes - Transaction log backups

Sample dates and time of restores:
6/2 - 5 PM Full backups (doesn't matter when it completes)
6/3 - 5 PM Differential backups (completes at 5:10 PM)

$RestoreTime (STOPAT) parameter set to 5:05 PM ('2018-06-03 17:05')

Restore-DbaDatabase -SqlInstance $RestoreTarget `
                    -DatabaseName $database `
                    -Path "\\$Source\$database" `
                    -DestinationDataDirectory $DataDir `
                    -DestinationLogDirectory $LogDir `
                    -RestoreTime $RestoreTime `
                    -MaintenanceSolutionBackup `
                    -WithReplace `
                    -AllowContinue `
                    -OutputScriptOnly | Out-File -FilePath $FilePath

Expected Behavior

Restore full backups from 6/2, then all corresponding log backups until 6/3 5:05 PM

Actual Behavior

Restores full backups from 6/2, differentials from 6/3, and one transaction log after5 PM ( until STOPAT 5:05 PM). The problem is the differential goes way beyond the restore point (because the backup completed at 5:10 PM and STOPAT within differential means nothing), then it tries to restore a previous log backup. This results in the error:

Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient.SqlError: The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point

Environmental data

• PowerShell:

Name                           Value
----                           -----
PSVersion                      5.1.14409.1012
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.14409.1012
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

• SQL Server:

 Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64) 
    Feb 21 2018 12:19:47 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

[Zhunya]

Have same behavior...

>Get-DbaBackupHistory -SqlInstance srv-sql-dr02 -Database DB1 | Select-DbaBackupInformation -RestoreTime '2018-06-04 22:05:21.000'

SqlInstance  Database     Type         TotalSize DeviceType Start                   Duration End
-----------  --------     ----         --------- ---------- -----                   -------- ---
SRV-SQL-DR02 DB1          Full         154,85 GB Disk       2018-06-01 22:22:57.000 00:10:17 2018-06-01 22:33:14.000
SRV-SQL-DR02 DB1          Differential 1,81 GB   Disk       2018-06-04 22:05:01.000 00:00:40 2018-06-04 22:05:41.000
SRV-SQL-DR02 DB1          Log          253,00 KB Disk       2018-06-04 22:07:00.000 00:00:00 2018-06-04 22:07:00.000

Bug in Select-DbaBackupInformation

[Zhunya]

Change line 138 in Select-DbaBackupInformation (and deleted allcommands.ps1)

Works in right way now. Can't make PR atm.

[amon09]

Hey Oleg, Thanks for the quick response. I really appreciate it. I haven't gotten a chance to test it out yet. I will try the workaround sometime next week and let you know.

Thanks again.

[niphlod]

technically what @Zhunya proposes is fine.
To allow a restore to a point in time, the last log is the one where start is greater than the point in time and end is lower than the point in time.
For the diff instead, since PITR with a diff is impossible, it should be selected the one that ends BEFORE the reference time, so additional log can be properly put in place to allow the PITR.

@amon09 : if you report it to work, we'll issue a relevant PR to fix the issue

[amon09]

@Zhunya @niphlod I did a quick check it does work as expected now.

Thanks a bunch for fixing this.