0

I am very fond of dbatools.io for SQL Server DBA maintenance

For some time now I have been using get-dbabackuphistory to check the backups of the last week on all databases / instances.

Currently we are in an upgrade phase to SQL Server 2017 and high availability groups and I noticed a possible bug in get-dbabackuphistory.

(dbatools Version 09.7.14)

When I check the backup history in a query in SSMS (on a secondary replica) I get a list of all full backups taken.

When using get-dbabackuphistory, the backup type is not correctly returned: I have a 'LOG' as type where it should be a 'FULL' type.

In a non-high availability environment, the info is correctly returned.

Are there other customers who noticed this? If it is a bug, can it be corrected?

  • 3
    Welcome to DBA.SE. We appreciate your participation. If it is a bug then you might be better off filing a bug report on the projects Github bug report page. Otherwise could you supply more information? Examples of the data returned by the dbatools.io script get-dbabackuphistory? Your server/instance/database setup? How you are backing up? Add this to your quesiton. – hot2use Feb 5 at 10:20
  • Hello,I noticed as well some info is completely missing – Daniel Reunes Feb 15 at 9:22
0

I noticed something else as well - I think some info is missing

some more details (example)

in the logfile of the full-backup

BACKUP DATABASE [MOA2] TO DISK = N'K:\MSSQL\BCK\MOA2\MOA2_backup_20190212_030146.BAK' WITH COPY_ONLY,NOFORMAT, NOINIT, NAME = N'MOA2_backup_20190212_030146', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10 28 percent processed. 57 percent processed. 85 percent processed. 92 percent processed. Processed 448 pages for database 'MOA2', file 'MOA2' on file 1. 100 percent processed. Processed 1 pages for database 'MOA2', file 'MOA2_log' on file 1. BACKUP DATABASE successfully processed 449 pages in 0.526 seconds (6.657 MB/sec).

when checking the backuploghistory with a t-sql in SSMS

SELECT s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, s.backup_start_date, CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn, CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn, CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType, s.server_name, s.recovery_model FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id WHERE s.database_name = 'MOA2' -- Remove this line for all the database ORDER BY backup_start_date DESC, backup_finish_date GO

extract of the output : database_name physical_device_name bkSize TimeTaken backup_start_date first_lsn last_lsn BackupType server_name recovery_model MOA2 K:\MSSQL\BCK\MOA2\MOA2_backup_20190215_030142.BAK 4 MB 0 Seconds 2019-02-15 03:02:35.000 3,3E+16 3,3E+16 Full VEWIPRO258\SQLACCBIV FULL MOA2 K:\MSSQL\BCK\MOA2\MOA2_backup_20190214_030118.BAK 4 MB 0 Seconds 2019-02-14 03:02:04.000 3,3E+16 3,3E+16 Full VEWIPRO258\SQLACCBIV FULL MOA2 K:\MSSQL\BCK\MOA2\MOA2_backup_20190213_030117.BAK 4 MB 1 Seconds 2019-02-13 03:03:44.000 3,3E+16 3,3E+16 Full VEWIPRO258\SQLACCBIV FULL MOA2 K:\MSSQL\BCK\MOA2\MOA2_backup_20190212_030146.BAK 4 MB 0 Seconds 2019-02-12 03:05:05.000 3,3E+16 3,3E+16 Full VEWIPRO258\SQLACCBIV FULL

when checking with get-dbabackuphistory

"VEWIPRO258\SQLACCBIV" | Get-DbaBackupHistory | where {$_.Database -eq "MOA2"}

ComputerName : VEWIPRO258 InstanceName : SQLACCBIV SqlInstance : VEWIPRO258\SQLACCBIV Database : MOA2 UserName : ARTISFB\sys_reunenda Start : 22/01/2019 10:43:07 End : 22/01/2019 10:43:07 Duration : 00:00:00 Path : {K:\muizen\bck\MOA2.BAK} TotalSize : 4,08 MB CompressedBackupSize : 600,97 KB CompressionRatio : 6,95 Type : Full BackupSetId : 34 DeviceType : Disk Software : Microsoft SQL Server FullName : {K:\muizen\bck\MOA2.BAK} FileList : {@{FileType=D; LogicalName=MOA2; PhysicalName=J:\MSSQL\data\MOA2.mdf}, @{FileType=L; LogicalName=MOA2_log; PhysicalName=L:\MSS QL\log\MOA2_log.ldf}} Position : 1 FirstLsn : 33000001884400037 DatabaseBackupLsn : 33000001881400037 CheckpointLsn : 33000001884400037 LastLsn : 33000001886100001 SoftwareVersionMajor : 12 IsCopyOnly : False LastRecoveryForkGUID : 595c648d-e73a-4e91-922a-3b57c28fe9a0 RecoveryModel : FULL

(the result is 1 record ; that is the info of a very old backup ... , not of the recent ones)

a lot of the info in the backuphistory is not shown

we recently moved to a high availability cluster with availability groups

and the database "MOA2" belongs to an availability group and "VEWIPRO258\SQLACCBIV" is the second replica

on the primary replica "VAWIPRO260\SQLACCBIV" I see for the results of the query

database_name physical_device_name bkSize TimeTaken backup_start_date first_lsn last_lsn BackupType server_name recovery_model MOA2 K:\MSSQL\BCK\MOA2\MOA2_backup_20190215_040142.TRN 0 MB 0 Seconds 2019-02-15 04:01:46.000 3,3E+16 3,3E+16 Transaction Log VAWIPRO260\SQLACCBIV FULL MOA2 K:\MSSQL\BCK\MOA2\MOA2_backup_20190214_040144.TRN 0 MB 0 Seconds 2019-02-14 04:01:46.000 3,3E+16 3,3E+16 Transaction Log VAWIPRO260\SQLACCBIV FULL MOA2 K:\MSSQL\BCK\MOA2\MOA2_backup_20190213_040141.TRN 0 MB 0 Seconds 2019-02-13 04:01:43.000 3,3E+16 3,3E+16 Transaction Log VAWIPRO260\SQLACCBIV FULL MOA2 K:\MSSQL\BCK\MOA2\MOA2_backup_20190212_040143.TRN 0 MB 0 Seconds 2019-02-12 04:01:45.000 3,3E+16 3,3E+16 Transaction Log VAWIPRO260\SQLACCBIV FULL MOA2 K:\MSSQL\BCK\MOA2\MOA2_backup_20190211_040146.TRN 0 MB 0 Seconds 2019-02-11 04:01:49.000 3,3E+16 3,3E+16 Transaction Log VAWIPRO260\SQLACCBIV FULL MOA2 K:\MSSQL\BCK\MOA2\MOA2_backup_20190210_040143.TRN 0 MB 0 Seconds 2019-02-10 04:01:46.000 3,3E+16 3,3E+16 Transaction Log VAWIPRO260\SQLACCBIV FULL MOA2 K:\MSSQL\BCK\MOA2\MOA2_backup_20190209_040145.TRN 0 MB 0 Seconds 2019-02-09 04:01:46.000 3,3E+16 3,3E+16 Transaction Log VAWIPRO260\SQLACCBIV FULL MOA2 K:\MSSQL\BCK\MOA2\MOA2_backup_20190208_040143.TRN 0 MB 0 Seconds 2019-02-08 04:01:45.000 3,3E+16 3,3E+16 Transaction Log VAWIPRO260\SQLACCBIV FULL

(on the primary replica we take transactionlog backups)

"VEWIPRO258\SQLACCBIV" | Get-DbaBackupHistory | where {$_.Database -eq "MOA2"} |export moa2.csv

extract of the csv-file

ComputerName InstanceName SqlInstance Database UserName Start End Duration Path TotalSize CompressedBackupSize CompressionRatio Type BackupSetId DeviceType Software FullName FileList Position FirstLsn DatabaseBackupLsn CheckpointLsn LastLsn SoftwareVersionMajor IsCopyOnly LastRecoveryForkGUID RecoveryModel VAWIPRO260 SQLACCBIV VAWIPRO260\SQLACCBIV MOA2 ARTISFB\ps_batch_bi 15/02/2019 4:01 15/02/2019 4:01 0:00:00 System.String[] 79,00 KB 7,04 KB 11,22 Log 561 Disk Microsoft SQL Server System.String[] System.Object[] 1 3,3E+16 3,3E+16 3,3E+16 3,3E+16 14 False 3e6248e0-3137-4c14-83a1-2afe081bed74 FULL VAWIPRO260 SQLACCBIV VAWIPRO260\SQLACCBIV MOA2 ARTISFB\ps_batch_bi 14/02/2019 4:01 14/02/2019 4:01 0:00:00 System.String[] 79,00 KB 7,22 KB 10,95 Log 541 Disk Microsoft SQL Server System.String[] System.Object[] 1 3,3E+16 3,3E+16 3,3E+16 3,3E+16 14 False 3e6248e0-3137-4c14-83a1-2afe081bed74 FULL VAWIPRO260 SQLACCBIV VAWIPRO260\SQLACCBIV MOA2 ARTISFB\ps_batch_bi 13/02/2019 4:01 13/02/2019 4:01 0:00:00 System.String[] 79,00 KB 7,20 KB 10,97 Log 521 Disk Microsoft SQL Server System.String[] System.Object[] 1 3,3E+16 3,3E+16 3,3E+16 3,3E+16 14 False 3e6248e0-3137-4c14-83a1-2afe081bed74 FULL

this info is correct

When working in a non-high availability cluster, the info is Always correct

So, what is the problem ? did I missed something or is get-dbabackuphistory missing something ?

regards Daniel Reunes

0

probably my backup procedrue has been changed recently - I noticed a 'WITH COPY_ONLY' cluase in my statement when adding -includeCopyOnly , I get all the info

Problem Solved

Sorry to have bothered anyone

Regards

Daniel Reunes

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.