Get-DbaBackupHistory is not always returning accurate last backup info correctly

[kmramsey]

Name    : dbatools
Path    : C:\windows\system32\WindowsPowerShell\v1.0\Modules\dbatools\dbatools.psd1
Version : 0.9.803

• [Done ] Verified errors are not related to permissions

Environmental data

Path    : C:\windows\system32\WindowsPowerShell\v1.0\Modules\dbatools\dbatools.psd1
Version : 0.9.803

SQL Server:

    Sep 22 2016 01:09:29 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Steps to Reproduce

--------------------------------------------------------------------------------- 
--FULL Database Backups for all databases For Previous Week 
--------------------------------------------------------------------------------- 
SELECT  top 1
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
and database_name = 'MYDBNAME'
and msdb.dbo.backupset.name = 'MYDBNAME - Full Backup'
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date desc

--------------------------------------------------------------------------------- 
--Diff Database Backups for all databases For Previous Week 
--------------------------------------------------------------------------------- 
SELECT top 1
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
and database_name = 'MYDBNAME'
and msdb.dbo.backupset.name = 'MYDBNAME - Diff Backup'
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date desc

--------------------------------------------------------------------------------- 
--Log Database Backups for all databases For Previous Week 
--------------------------------------------------------------------------------- 
SELECT top 1
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
and database_name = 'MYDBNAME'
and msdb.dbo.backupset.name = 'MYDBNAME - Log Backup'
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date desc

PS D:\SysAdmin\Scripts> Get-Dbadatabase -SqlInstance "MYinstance" -Database MYDBNAME -Verbose:$false

ComputerName       : MYinstance
InstanceName       : MD
SqlInstance        : MYinstance\MD
Name               : MYDBNAME
Status             : Normal
IsAccessible       : True
RecoveryModel      : Full
LogReuseWaitStatus : LogBackup
SizeMB             : 375.125
Compatibility      : Version120
Collation          : SQL_Latin1_General_CP1_CI_AS
Owner              : MyOwner
LastFullBackup     : 4/21/2019 3:21:16 PM === Different from get-dbabackuphistory - Get-Dbadatabase is correct.
LastDiffBackup     : 4/20/2019 3:10:07 PM === Different from get-dbabackuphistory - Get-Dbadatabase is correct.
LastLogBackup      : 4/22/2019 1:20:19 PM === Different from get-dbabackuphistory - Get-Dbadatabase is correct.

PS D:\SysAdmin\Scripts> Get-DbaBackupHistory -SqlInstance "MYinstance" -Database MYDBNAME  -Verbose:$false -WarningAction SilentlyContinue -LastFull -IncludeCopyOnly | Select Database, Type, START, END, path | ft

Database Type Start               End                 Path                                                                         
-------- ---- -----               ---                 ----                                                                         
MYDBNAME      Full 4/7/2019 3:35:43 PM 4/7/2019 3:36:03 PM {\\MYinstance\Backups\Sunday\MYinstance.MYDBNAME_2019-04-07_15-35-43_Full.BAK}

PS D:\SysAdmin\Scripts> Get-DbaBackupHistory -SqlInstance "MYinstance" -Database MYDBNAME  -Verbose:$false -WarningAction SilentlyContinue -LastLog | Select Database, Type, START, END, path | ft

Database Type Start                End                  Path                                                                               
-------- ---- -----                ---                  ----                                                                               
MYDBNAME      Log  4/11/2019 1:20:20 PM 4/11/2019 1:20:20 PM {\\MYinstance\Backups\Thursday\Logs\MYinstance.MYDBNAME_2019-04-11_13-20-19_Log.TRN}

PS D:\SysAdmin\Scripts> Get-DbaBackupHistory -SqlInstance "MYinstance" -Database MYDBNAME  -Verbose:$false -WarningAction SilentlyContinue -LastDiff | Select Database, Type, START, END, path | ft

Database Type         Start                End                  Path                                                                            
-------- ----         -----                ---                  ----                                                                            
MYDBNAME      Differential 4/10/2019 3:07:21 PM 4/10/2019 3:07:23 PM {\\MYinstance\Backups\Wednesday\MYinstance.MYDBNAME_2019-04-10_15-07-21_Diff.BAK}

Expected Behavior

Correct date of last backups

Actual Behavior

Inconsistent results. When I run these commands for 150 databases, I get 4-6 databases with incorrect backup information. When I isolate the ones with incorrect info, its consistently incorrrect. When pick good databases, its consistently correct. In other words, its not randomly inconsistent.

[kmramsey]

I've created a better script that helps uncover the problem.

$MyDatabase = "YourDatabase"

Get-Dbadatabase -SqlInstance $Myinstance -Database $MyDatabase
Get-DbaBackupHistory -SqlInstance $Myinstance -Database $MyDatabase  -Verbose:$false -WarningAction SilentlyContinue -LastFull -IncludeCopyOnly | Select Database, Type, START, END, path | ft
Get-DbaBackupHistory -SqlInstance $Myinstance -Database $MyDatabase  -Verbose:$false -WarningAction SilentlyContinue -LastLog | Select Database, Type, START, END, path | ft
Get-DbaBackupHistory -SqlInstance $Myinstance -Database $MyDatabase  -Verbose:$false -WarningAction SilentlyContinue -LastDiff | Select Database, Type, START, END, path | ft

$queryDatabaseBackupInfo1=
"--------------------------------------------------------------------------------- 
--FULL Database Backups for all databases For Previous Week 
--------------------------------------------------------------------------------- 
SELECT  top 1
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
and database_name = '$MyDatabase'
and msdb.dbo.backupset.name = '$MyDatabase - Full Backup'
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date desc
"
$queryDatabaseBackupInfo2 = 
"
SELECT top 1
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
and database_name = '$MyDatabase'
and msdb.dbo.backupset.name = '$MyDatabase - Diff Backup'
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date desc
"

$queryDatabaseBackupInfo3 = 
"
SELECT top 1
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
and database_name = '$MyDatabase'
and msdb.dbo.backupset.name = '$MyDatabase - Log Backup'
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date desc
"

Invoke-DbaQuery -SqlInstance $Myinstance -Database msdb -Query $queryDatabaseBackupInfo1
Invoke-DbaQuery -SqlInstance $Myinstance -Database msdb -Query $queryDatabaseBackupInfo2
Invoke-DbaQuery -SqlInstance $Myinstance -Database msdb -Query $queryDatabaseBackupInfo3

[niphlod]

we order by lsns, not finish date .... sure those "inconsistent result" are not coming from forks ?

[kmramsey]

Forked: I don't use forked versions of dbatools.
Oder by: With respect, I'm not sure what your order by comment. When I ask for the last log, or last diff, or last full, it should get you the actual LAST full, diff or log, right?

[niphlod]

forks as in "backup forks". We do have a "long standing" issue that seems to come up over and over with the way we order things..... as for "I ask for the last", be aware that is not always that easy to go for finish_date, so we use a more "strict" approach ordering by LSNs .... Problem is that LSNs unconveniently are not reset for backup forks... which in turn makes the order "screwed" for those databases which have a recorded history of a backup fork ;-(

[kmramsey]

Do you have any TSQL or Powershell that would help me know if that scenario is happening?

[kmramsey]

I don't think this should be stale.

[kmramsey]

Pretty please keep this issue alive.

[wsmelton]

We need to see the LSNs of those backups because as stated, we do not go by dates on the backups in our commands. We look at whether forks occurred in the backups of any given database:

https://github.com/sqlcollaborative/dbatools/blob/1b58e85313709510577a9695c8ee6dbe5e113bd5/functions/Get-DbaDbBackupHistory.ps1#L254-L293

[kmramsey]

You guys understand this better than I do. Are you saying this ticket is not needed? Do you need me to give you something to investigate?

[wsmelton]

Run the query below on a database you have that is showing "bad" information. If you get any results back that is why the results from our command are going to be inconsistent.

SELECT 
                                     database_name, 
                                     MIN(database_backup_lsn) as 'FirstLsn', 
                                     MAX(database_backup_lsn) as 'FinalLsn', 
                                     MIN(backup_start_date) as 'MinDate', 
                                     MAX(backup_finish_date) as 'MaxDate', 
                                     last_recovery_fork_guid 'RecFork', 
                                     count(1) as 'backupcount' 
                                 FROM msdb.dbo.backupset 
                                 WHERE database_name='<your database name here>' 
                                 GROUP by database_name, last_recovery_fork_guid 
                                 ORDER by MaxDate Asc

[rferraton]

keep alive

[wsmelton]

This is not going to be something we can resolve without access to a system that shows the issue.

If someone wants to provide a data dump of all the backup history for thier environment, or a backup of the msdb database itself would be appreciated.

[potatoqualitee]

mayyyybe what we can do, since a number of people have a concern, is offer an -OrderBy parameter. We default to LSN but then maybe if we encounter the chain issue X times, we write in Verbose "try OrderBy LastDate if you continue to encounter this issue.

I get that fork problem so often during demos, i'd probably just set OrderBy LastDate in my PSDefaultParameterValues

[TakeshiKovacs]

It would be useful if we could select which LSN it is looking at. We currently have it reporting a wrong backup as being the most recent as it is ordering by the FirstLSN field, if it sorted on either the DatabaseBackupLSN or LastLSN it would be correct in our situation.

[andreasjordan]

Let's link #8604 here as this pr should help so solve this issue.

[andreasjordan]

But the linked pr only changes Get-DbaAgBackupHistory - so do we want this for Get-DbaBackupHistory as well?

[potatoqualitee]

LSNSort in the backup history? Yes, that'd be awesome, @andreasjordan

[potatoqualitee]

dope, this is closed as fixed! will be releasd in 2.0

[wsmelton]

There has been no fix to this placed in the Get-DbaDbBackupHistory command, the linked PR is for the AG backup history command.

[potatoqualitee]

oh, thanks Shawn. I'm pretty much down to skipping many of these as a requirement for 2.0 and docs will be the final requirement.

[potatoqualitee]

i dont understand how to fix it but if anyone else wants to, i imagine dbatools 2.0 will be GA within 30 days.

[potatoqualitee]

Wait, this was fixed? I truncated the code a lil bit to show

https://github.com/dataplat/dbatools/blob/development/public/Get-DbaDbBackupHistory.ps1#L179