Get-DbaBackupHistory doesn't handle striped backups neatly #679

Open
Stuart-Moore opened this Issue Feb 8, 2017 · 2 comments

Projects

None yet

4 participants

@Stuart-Moore
Contributor

Is this a feature OR bug:

Bug?

It's returning data in a format that I don't think is correct for it's usage

System Details

  • Operating system name and version:
    Major Minor Build Revision

10 0 14393 0

  • Output from $PSVersionTable:
    Name Value

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

  • Output of dbatools version:
    0.8.702

  • SQL Server version for source/target

Steps to Reproduce

Get-DbaBackupHistory -SqlServer localhost\sqlexpress2016 -Databases IndexPresentation | ?{$_.Type -eq 'Full'}

(backed up using striped backups)

Action Results

Server : localhost\sqlexpress2016
Database : INDEXPRESENTATION
Username : Stuart
Start : 31/01/2017 08:57:56
End : 31/01/2017 08:57:58
Duration : 2 Seconds
Path : C:\TEMP\IP1.BAK
TotalSizeMB : 236.21
Type : Full
MediaSetId : 3080
DeviceType : Disk
Software : Microsoft SQL Server

Server : localhost\sqlexpress2016
Database : INDEXPRESENTATION
Username : Stuart
Start : 31/01/2017 08:57:56
End : 31/01/2017 08:57:58
Duration : 2 Seconds
Path : C:\TEMP\IP2.BAK
TotalSizeMB : 236.21
Type : Full
MediaSetId : 3080
DeviceType : Disk
Software : Microsoft SQL Server

Server : localhost\sqlexpress2016
Database : INDEXPRESENTATION
Username : Stuart
Start : 31/01/2017 08:57:56
End : 31/01/2017 08:57:58
Duration : 2 Seconds
Path : C:\TEMP\IP3.BAK
TotalSizeMB : 236.21
Type : Full
MediaSetId : 3080
DeviceType : Disk
Software : Microsoft SQL Server

Expected Results

Server : localhost\sqlexpress2016
Database : INDEXPRESENTATION
Username : Stuart
Start : 31/01/2017 08:57:56
End : 31/01/2017 08:57:58
Duration : 2 Seconds
Path : {C:\TEMP\IP1.BAK, C:\TEMP\IP2.BAK, C:\TEMP\IP3.BAK}
TotalSizeMB : 236.21
Type : Full
MediaSetId : 3080
DeviceType : Disk
Software : Microsoft SQL Server

ie; all the files for that single backup to be reported as 1 backup, not as 3 separate backups

@ctrlbold
Member
ctrlbold commented Feb 8, 2017

mmmmmmmmmmm I contemplated this and went with the rawest. Perhaps we can keep the current format as -Raw and your proposed format as default output because it's the most accurate. We'd also need to update any commands that use this command as it expects this output (probably).

Whoever does the rollup, please ensure Start is the earliest Start and End is the latest End.

@SirCaptainMitch SirCaptainMitch added the bug label Feb 9, 2017
@sanderstad
Contributor

This could be easily fixed by saving the values in a temporary table first and than use FOR XML to get the paths into a list:
SELECT BackupSetRank , Server , [Database] , Username , Start , [End] , Duration , STUFF((SELECT ',' + Path FROM #backuphistory t2 WHERE t2.MediaSetId = b.MediaSetId AND t2.Start = b.Start FOR XML PATH('') ,TYPE).value('.', 'varchar(max)'), 1, 1, '') AS Path , Type , TotalSizeMB , MediaSetId , Software FROM #backuphistory AS b GROUP BY b.BackupSetRank , b.Server , b.[Database] , b.Username , b.Start , b.[End] , b.Duration , b.Type , b.TotalSizeMB , b.MediaSetId , b.Software;

@Stuart-Moore Stuart-Moore self-assigned this Feb 21, 2017
@Stuart-Moore Stuart-Moore referenced this issue Feb 22, 2017
Merged

Get dba backup history 679 #794

0 of 18 tasks complete
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment