[Bug]: Get-DbaDbBackupHistory (Break in LSN chain)

[saxet77]

Report

Host used

• [ ] powershell.exe
• [x] ISE
• [ ] VS Code
• [ ] Other (please specify)

Errors Received

WARNING: [21:10:15][Restore-DbaDatabase] Database TODELETE failed testing,  skipping

Steps to Reproduce

# Get backup history from the SourceInstance msdb database
$ListBackupFile = Get-DbaDbBackupHistory -SqlInstance Server1 -Database Database1 -last 
  
 # Generate the PIT restore script
 $RestoreScript = $ListBackupFile | Restore-DbaDatabase -SqlInstance Server2 -DatabaseName TODELETE -RestoreTime '2021-07-07 01:17:15' -WithReplace -OutputScriptOnly -ReplaceDbNameInFile -TrustDbBackupHistory;

$ListBackupFile
SqlInstance                 Database   Type TotalSize   DeviceType Start                   Duration End                    
-----------                 --------   ---- ---------   ---------- -----                   -------- ---                    

#### Expected Behavior

Restore Script provide by SSMS 'Restore/Database/Script'

Actual Behavior

Restore Script provide by DBATools (missing 1 log file)

Environmental information

🚨🚨🚨🚨🚨🚨🚨🚨🚨🚨 Please provide the output of the below script

Powershell Version = 5.1.14393.3866
dbatools lastest installed = 1.0.169
Culture of OS = 
LCID             Name             DisplayName                                                                                                                                                                                                                              
----             ----             -----------                                                                                                                                                                                                                              
1036             fr-FR            French (France)

SQL Server:

Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64)   Feb  9 2021 12:08:50   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor) 

us_english

[saxet77]

Expected Behavior generate from the SSMS 'Database/Restore/Script'

Actual behavior generated by DBATools (seems missing 1 log file)

[saxet77]

Include the msdb..backupset table extract for the database/log backup LSN. DatabaseBackupLSN are the same for the backup database and the missing backup log. Peharps it's the problem ...

[andreasjordan]

Currently I can not reproduce this. I just had a look at the code: BackupDatabaseLSN is not used.

[andreasjordan]

Does it return the correct script if you don't use -RestoreTime?

[andreasjordan]

If you look at $ListBackupFile - the file is missing already there - correct?

[andreasjordan]

Sorry for all the messages - I think I found the problem...

[andreasjordan]

@saxet77 - Can you please verify that you get the correct result from Get-DbaDbBackupHistory if you add -IncludeCopyOnly, so using $ListBackupFile = Get-DbaDbBackupHistory -SqlInstance Server1 -Database Database1 -last -IncludeCopyOnly?

@Stuart-Moore - Can you tell me more about this part? https://github.com/sqlcollaborative/dbatools/blob/d81f07cc176102dda4c48ae620953d0f62ed3cb6/functions/Get-DbaDbBackupHistory.ps1#L340-L345 Especially the difference in the Where-Object: [bigint]$_.DatabaseBackupLSN -eq [bigint]$fullDb.CheckPointLSN.

[andreasjordan]

About your situation: The full backup finished after the log backup started, but before the log backup finished. That's why the missing log backup has a different DatabaseBackupLSN than the other log backup and then CheckpointLSN of the full backup. Because of that, the missing log backup is not been taken into account.

[saxet77]

@andreasjordan, I will use -IncludeCopyOnly in my script.

You think it's need a fix on the Get-DbaDbBackupHistory cmdlet ?

[andreasjordan]

Yes, we have to fix Get-DbaDbBackupHistory. Your case (first log backup started before full finishes but finishes after full) is a rare case and thus has not seen in tests until now. I don't understand the code difference between having IncludeCopyOnly set and not set yet. Buit I see using -IncludeCopyOnly as a workaround for you and I will try to find time to dig deep into that or maybe @Stuart-Moore has time as he is the author of that command.

[andreasjordan]

Can you add -StopAfterGetBackupInformation -GetBackupInformation backupInformation to the command? Then the variable $backupInformation is filled with the backups that would be restored. I would like to know if the "missing log backup" is included there or not. So to have an idea where it gets filtered out.

[andreasjordan]

So here the $FilteredBackupHistory (that is what is in your $backupinformation) is still including the "missing log backup": https://github.com/sqlcollaborative/dbatools/blob/879021234ae8730f4550d75f1ee43397b6b673a7/functions/Restore-DbaDatabase.ps1#L705

Test-DbaBackupInformation is then building an internal $DbHistory which is passed to Test-DbaLsnChain and failes there because of the missing log backup.

Ok, I'll have a deeper look into Test-DbaBackupInformation...

[andreasjordan]

Can you please run the command that fails (edit: that writes the warning about the brocken chain) with -Verbose - maybe that output helps us.

[andreasjordan]

Thanks for the verbose output - but I still don't see where the log backup gets filtered out. We have to wait for @Stuart-Moore - maybe he has an idea.

[Stuart-Moore]

@andreasjordan
DatabaseBackupLSN is the LSN of the last 'real' full backup taken before the current backup was taken.

CheckpointLSN is usuallly the same as FirstLSN and is the LSN to which the backup would be restored to. It's rare to not have them be the same but it can happen

Reading those 2 lines I can't remember why they're different. Again, i expect it was another edge case that cropped up

@saxet77 is it possible for you to share you backup history? Then we can wrap it into the tests to make it easier to play with/replicate

If you could just run:

| Export-CliXml -Path c:\somewhere\file.xml

and upload it to here, then we can rehydrate it and work with. If you want to obfuscate server and db names that's fine. Really just want the LSNs and the other metadata.

[Stuart-Moore]

sorry, mucked up the Markdown in the last comment. Pleae could you upload: $ListBackupFile | Export-CliXml -Depth 5 -Path c:\somewhere\file.xml

I can fake something if needed, but having 'real' data will make sure we have a proper test to stop it coming back again

[Stuart-Moore]

Going go need a bit more info please, was concentrating on the restore side to much. Could you dump out the raw backup history for me please? Will need something like: Get-DbaDbBackupHistory -sqlinstance x -database y -since '10/07/2021 21:10' | Export-CliXml -path -depth 5 ./some.xml

Sorry for the delay, time is being eaten up by a large migration atm.

[Stuart-Moore]

Think i now have a fix for this, but it's failing a single test. Need to rebuild my local test rig to work out why.

[Stuart-Moore]

Ok, so this is getting annoying. Passes fine locally:

But fails in Appveyor.

Trying to rdp in and it appears to not be liking the password. Is it not just the one in appveyor.yml anymore? (@andreasjordan or @potatoqualitee can you help here?)

[potatoqualitee]

oh sorry, ill msg you on twitter, stuart

[Stuart-Moore]

Gah, it's a bug with 2008r2! That's why it works fine for me.

For some reason 2008r2 returns 3 rows of history, others return 4. Both result sets are restoreable though, so that's all that really matters. will fix up tests and push it back up

[potatoqualitee]

haha awesome work! 🚒

[Stuart-Moore]

Yeah, that was confusing the heck out of me. The good news is that the filtering of all back up history is done by select-dbabackupinformation, so we should have consistency between get-dbadbbackuphistory and the restore stack.

@saxet77 Really sorry for the delay on this one. Would you be able to take this new branch for a spin and see if it fixes your issue. I think it should, but would be great to know it does.

[potatoqualitee]

Thanks, @Stuart-Moore ! I'll wait for your PR to release 1.1.0 💯

[saxet77]

Hi @Stuart-Moore, no problem for the delay. Not very familiar with GitHub "how can I take this new branch" to validate the fix on my side ? Thks

[Stuart-Moore]

I can walk you through it. Do you have any git experience? You won't need much but just means i know where to target things

[potatoqualitee]

Is the fix in the dev branch? if so, it can be downloaded from https://dbatools.io/devzip

[Stuart-Moore]

Not yet. Worst case I can share a one drive link to a zip of the lsnbreak branch.

Sent from a mobile device so apologies for any spolling mistakes

On 22 Jul 2021, at 20:13, Chrissy LeMaire @.***> wrote:

 Is the fix in the dev branch? if so, it can be downloaded from https://dbatools.io/devzip

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[potatoqualitee]

Ahh there's the branch! Here's the zip

https://github.com/sqlcollaborative/dbatools/archive/refs/heads/lsnbreak.zip

[saxet77]

Thks, got the zip file. tests in progress ... ;)

[potatoqualitee]

sweet! looking forward to hearing back

[Stuart-Moore]

Great, and I’ve learnt a new Github feature as well 👍

Cheers Stuart On 23 Jul 2021, 06:49 +0100, Chrissy LeMaire @.***>, wrote:

sweet! looking forward to hearing back — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[potatoqualitee]

Through the website, you can select the branch, then click on the green button and copy the link from Download zip 👍🏼

[potatoqualitee]

hey @saxet77 - please close out powershell entirely then Import-module C:\wherever\to\the\dir\dbatools.psd1 -Force

without a force, it can look like the new module files are imported, but they arent.

[potatoqualitee]

bummer. thank you for the files, tho. they may be able to help stuart 💯

[Stuart-Moore]

Take 2. Would you mind taking this new version for a spin. It seems to work on the 'msdb' I've hacked together from the files, but need to know it works with a proper one please

https://github.com/sqlcollaborative/dbatools/archive/refs/heads/lsnbreak.zip

[saxet77]

Sorry ;( Same error message with this new version.

[andreasjordan]

@Stuart-Moore Do you have an eye on this?