functions/Get-DbaBackupHistory.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
#ValidationTags#Messaging,FlowControl,Pipeline,CodeStyle#
function Get-DbaBackupHistory {
    <#
  .SYNOPSIS
   Returns backup history details for databases on a SQL Server.
   
  .DESCRIPTION
   Returns backup history details for some or all databases on a SQL Server.
    
   You can even get detailed information (including file path) for latest full, differential and log files.
 
   Backups taken with the CopyOnly option will NOT be returned, unless the IncludeCopyOnly switch is present
    
   Reference: http://www.sqlhub.com/2011/07/find-your-backup-history-in-sql-server.html
  
   .PARAMETER SqlInstance
         SQL Server name or SMO object representing the SQL Server to connect to. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.
 
     .PARAMETER SqlCredential
         Credential object used to connect to the SQL Server Instance as a different user. This can be a Windows or SQL Server account. Windows users are determined by the existence of a backslash, so if you are intending to use an alternative Windows connection instead of a SQL login, ensure it contains a backslash.
  
  .PARAMETER Database
   Specifies one or more database(s) to process. If unspecified, all databases will be processed.
 
  .PARAMETER ExcludeDatabase
   Specifies one or more database(s) to exclude from processing.
  
  .PARAMETER IncludeCopyOnly
   By default Get-DbaBackupHistory will ignore backups taken with the CopyOnly option. This switch will include them
   
  .PARAMETER Force
   If this switch is enabled, a large amount of information is returned, similar to what SQL Server itself returns.
   
  .PARAMETER Since
   Specifies a Datetimeobject to use as the starting point for the search for backups.
   
  .PARAMETER Last
   If this switch is enabled, the most recent full chain of full, diff and log backup sets is returned.
   
  .PARAMETER LastFull
   If this switch is enabled, the most recent full backup set is returned.
   
  .PARAMETER LastDiff
   If this switch is enabled, the most recent differential backup set is returned.
   
  .PARAMETER LastLog
   If this switch is enabled, the most recent log backup is returned.
   
  .PARAMETER DeviceType
   Specifieds a filter for backupsets based on DeviceTypees. Valid options are 'Disk','Permanent Disk Device', 'Tape', 'Permanent Tape Device','Pipe','Permanent Pipe Device','Virtual Device', in addition to custom integers for your own DeviceTypes.
   
  .PARAMETER Raw
   If this switch is enabled, one object per backup file is returned. Otherwise, mediasets (striped backups across multiple files) will be grouped into a single return object.
   
  .PARAMETER Type
   Specifies one or more types of backups to return. Valid options are 'Full', 'Log', 'Differential', 'File', 'Differential File', 'Partial Full', and 'Partial Differential'. Otherwise, all types of backups will be returned unless one of the -Last* switches is enabled.
   
  .PARAMETER LastLsn
   Specifies a minimum LSN to use in filtering backup history. Only backups with an LSN greater than this value will be returned, which helps speed the retrieval process.
   
  .PARAMETER EnableException
   By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
   This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
   Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
    
  .EXAMPLE
   Get-DbaBackupHistory -SqlInstance SqlInstance2014a
    
   Returns server name, database, username, backup type, date for all backups databases on SqlInstance2014a. This may return many rows; consider using filters that are included in other examples.
   
  .EXAMPLE
   $cred = Get-Credential sqladmin
   Get-DbaBackupHistory -SqlInstance SqlInstance2014a -SqlCredential $cred
    
   Does the same as above but logs in as SQL user "sqladmin"
   
  .EXAMPLE
   Get-DbaBackupHistory -SqlInstance SqlInstance2014a -Database db1, db2 -Since '7/1/2016 10:47:00'
    
   Returns backup information only for databases db1 and db2 on SqlInstance2014a since July 1, 2016 at 10:47 AM.
   
  .EXAMPLE
   Get-DbaBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014, pubs -Force | Format-Table
    
   Returns information only for AdventureWorks2014 and pubs and formats the results as a table.
   
  .EXAMPLE
   Get-DbaBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -Last
    
   Returns information about the most recent full, differential and log backups for AdventureWorks2014 on sql2014.
   
  .EXAMPLE
   Get-DbaBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -Last -DeviceType Disk
    
   Returns information about the most recent full, differential and log backups for AdventureWorks2014 on sql2014, but only for backups to disk.
   
  .EXAMPLE
   Get-DbaBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -Last -DeviceType 148,107
    
   Returns information about the most recent full, differential and log backups for AdventureWorks2014 on sql2014, but only for backups with device_type 148 and 107.
   
  .EXAMPLE
   Get-DbaBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -LastFull
    
   Returns information about the most recent full backup for AdventureWorks2014 on sql2014.
   
  .EXAMPLE
   Get-DbaBackupHistory -SqlInstance sql2014 -Database AdventureWorks2014 -Type Full
    
   Returns information about all Full backups for AdventureWorks2014 on sql2014.
   
  .EXAMPLE
   Get-DbaRegisteredServer -SqlInstance sql2016 | Get-DbaBackupHistory
    
   Returns database backup information for every database on every server listed in the Central Management Server on sql2016.
   
  .EXAMPLE
   Get-DbaBackupHistory -SqlInstance SqlInstance2014a, sql2016 -Force
    
   Returns detailed backup history for all databases on SqlInstance2014a and sql2016.
   
  .NOTES
   Tags: Storage, DisasterRecovery, Backup
   dbatools PowerShell module (https://dbatools.io, clemaire@gmail.com)
   Copyright (C) 2016 Chrissy LeMaire
   License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0    
   
  .LINK
   https://dbatools.io/Get-DbaBackupHistory
 #>

    [CmdletBinding(DefaultParameterSetName = "Default")]
    Param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter[]]
        $SqlInstance,
        [Alias("Credential")]
        [PsCredential]$SqlCredential,
        [Alias("Databases")]
        [object[]]$Database,
        [object[]]$ExcludeDatabase,
        [switch]$IncludeCopyOnly,
        [Parameter(ParameterSetName = "NoLast")]
        [switch]$Force,
        [Parameter(ParameterSetName = "NoLast")]
        [DateTime]$Since,
        [Parameter(ParameterSetName = "Last")]
        [switch]$Last,
        [Parameter(ParameterSetName = "Last")]
        [switch]$LastFull,
        [Parameter(ParameterSetName = "Last")]
        [switch]$LastDiff,
        [Parameter(ParameterSetName = "Last")]
        [switch]$LastLog,
        [string[]]$DeviceType,
        [switch]$Raw,
        [bigint]$LastLsn,
        [ValidateSet("Full", "Log", "Differential", "File", "Differential File", "Partial Full", "Partial Differential")]
        [string[]]$Type,
        [Alias('Silent')]
        [switch]$EnableException
    )
    
    begin {
        Write-Message -Level System -Message "Active Parameterset: $($PSCmdlet.ParameterSetName)."
        Write-Message -Level System -Message "Bound parameters: $($PSBoundParameters.Keys -join ", ")"
    

        $DeviceTypeMapping = @{
            'Disk'                  = 2
            'Permanent Disk Device' = 102
            'Tape'                  = 5
            'Permanent Tape Device' = 105
            'Pipe'                  = 6
            'Permanent Pipe Device' = 106
            'Virtual Device'        = 7
        }
        $DeviceTypeFilter = @()
        foreach ($DevType in $DeviceType) {
            if ($DevType -in $DeviceTypeMapping.Keys) {
                $DeviceTypeFilter += $DeviceTypeMapping[$DevType]
            }
            else {
                $DeviceTypeFilter += $DevType
            }
        }
        $BackupTypeMapping = @{
            'Log'                  = 'L'
            'Full'                 = 'D'
            'File'                 = 'F'
            'Differential'         = 'I'
            'Differential File'    = 'G'
            'Partial Full'         = 'P'
            'Partial Differential' = 'Q'
        }
        $BackupTypeFilter = @()
        foreach ($TypeFilter in $Type) {
            $BackupTypeFilter += $BackupTypeMapping[$TypeFilter]
        }
    
    }
    
    process {
        foreach ($instance in $SqlInstance) {
            
            try {
                Write-Message -Level VeryVerbose -Message "Connecting to $instance." -Target $instance
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $SqlCredential
            }
            catch {
                Stop-Function -Message "Failed to process Instance $Instance." -InnerErrorRecord $_ -Target $instance -Continue
            }
            
            if ($server.VersionMajor -lt 9) {
                Stop-Function -Message "SQL Server 2000 not supported." -Category LimitsExceeded -Target $instance -Continue
            }
            $backupSizeColumn = 'backup_size'
            if ($server.VersionMajor -ge 10) {
                # 2008 introduced compressed_backup_size
                $backupSizeColumn = 'compressed_backup_size'
            }
            
            $databases = @()
            if ($null -ne $Database) {
                ForEach ($db in $Database) {
                    $databases += [PScustomObject]@{name = $db}
                }
            }
            else {
                $databases = $server.Databases    
            }
            if ($ExcludeDatabase) {
                $databases = $databases | Where-Object Name -NotIn $ExcludeDatabase
            }
            foreach ($d in $DeviceTypeFilter) {
                $DeviceTypeFilterRight = "IN ('" + ($DeviceTypeFilter -Join "','") + "')"
            }
            
            foreach ($b in $BackupTypeFilter) {
                $BackupTypeFilterRight = "IN ('" + ($BackupTypeFilter -Join "','") + "')"
            }

            if ($last) {
                
                foreach ($db in $databases) {
                    
                    #Get the full and build upwards
                    $allbackups = @()
                    $allbackups += $Fulldb = Get-DbaBackupHistory -SqlInstance $server -Database $db.Name -LastFull -raw:$Raw -DeviceType $DeviceType -IncludeCopyOnly:$IncludeCopyOnly
                    $DiffDB = Get-DbaBackupHistory -SqlInstance $server -Database $db.Name -LastDiff -raw:$Raw -DeviceType $DeviceType -IncludeCopyOnly:$IncludeCopyOnly
                    if ($DiffDb.LastLsn -gt $Fulldb.LastLsn -and  $DiffDb.DatabaseBackupLSN -eq $Fulldb.CheckPointLSN ) {
                        Write-Message -Level Verbose -Message "Valid Differential backup "
                        $Allbackups += $DiffDB
                        $TLogStartLSN = ($diffdb.FirstLsn -as [bigint])
                    }
                    else {
                        Write-Message -Level Verbose -Message "No Diff found"
                        try {
                            [bigint]$TLogStartLSN = $Fulldb.FirstLsn.ToString() 
                        }
                        catch {
                            continue
                        }
                    }
                    $Allbackups += $Logdb = Get-DbaBackupHistory -SqlInstance $server -Database $db.Name -raw:$raw -DeviceType $DeviceType -LastLsn $TLogstartLSN -IncludeCopyOnly:$IncludeCopyOnly | Where-Object { 
                        $_.Type -eq 'Log' -and [bigint]$_.LastLsn -gt [bigint]$TLogstartLSN -and [bigint]$_.DatabaseBackupLSN -eq [bigint]$Fulldb.CheckPointLSN -and $_.LastRecoveryForkGuid -eq $Fulldb.LastRecoveryForkGuid
                    }
                    #This line does the output for -Last!!!
                    $Allbackups |  Sort-Object -Property LastLsn, Type
                
                }
                continue
            }
            
            if ($LastFull -or $LastDiff -or $LastLog) {
                #$sql = @()
                if ($LastFull) {
                    $first = 'D'; $second = 'P'
                }
                if ($LastDiff) {
                    $first = 'I'; $second = 'Q'
                }
                if ($LastLog) {
                    $first = 'L'; $second = 'L'
                }
                $databases = $databases | Select-Object -Unique
                foreach ($db in $databases) {
                    Write-Message -Level Verbose -Message "Processing $($db.name)" -Target $db
                    $wherecopyonly = $null
                    if ($true -ne $IncludeCopyOnly) {
                        $wherecopyonly = " AND is_copy_only='0' "
                    }
                    if ($DeviceTypeFilter) {
                        $DevTypeFilterWhere = "AND mediafamily.device_type $DeviceTypeFilterRight"
                    }
                    $sql += "
        SELECT
         a.BackupSetRank,
         a.Server,
         a.[Database],
         a.Username,
         a.Start,
         a.[End],
         a.Duration,
         a.[Path],
         a.Type,
         a.TotalSize,
         a.MediaSetId,
         a.BackupSetID,
         a.Software,
          a.position,
          a.first_lsn,
          a.database_backup_lsn,
          a.checkpoint_lsn,
          a.last_lsn,
         a.first_lsn as 'FirstLSN',
          a.database_backup_lsn as 'DatabaseBackupLsn',
          a.checkpoint_lsn as 'CheckpointLsn',
          a.last_lsn as 'Lastlsn',
          a.software_major_version,
         a.DeviceType,
         a.is_copy_only,
         a.last_recovery_fork_guid
        FROM (SELECT
          RANK() OVER (ORDER BY backupset.last_lsn desc, backupset.backup_finish_date DESC) AS 'BackupSetRank',
          backupset.database_name AS [Database],
          backupset.user_name AS Username,
          backupset.backup_start_date AS Start,
          backupset.server_name as [Server],
          backupset.backup_finish_date AS [End],
          DATEDIFF(SECOND, backupset.backup_start_date, backupset.backup_finish_date) AS Duration,
          mediafamily.physical_device_name AS Path,
          backupset.$backupSizeColumn AS TotalSize,
          CASE backupset.type
         WHEN 'L' THEN 'Log'
         WHEN 'D' THEN 'Full'
         WHEN 'F' THEN 'File'
         WHEN 'I' THEN 'Differential'
         WHEN 'G' THEN 'Differential File'
         WHEN 'P' THEN 'Partial Full'
         WHEN 'Q' THEN 'Partial Differential'
         ELSE NULL
          END AS Type,
          backupset.media_set_id AS MediaSetId,
          mediafamily.media_family_id as mediafamilyid,
          backupset.backup_set_id as BackupSetID,
          CASE mediafamily.device_type
         WHEN 2 THEN 'Disk'
         WHEN 102 THEN 'Permanent Disk Device'
         WHEN 5 THEN 'Tape'
         WHEN 105 THEN 'Permanent Tape Device'
         WHEN 6 THEN 'Pipe'
         WHEN 106 THEN 'Permanent Pipe Device'
         WHEN 7 THEN 'Virtual Device'
         ELSE 'Unknown'
         END AS DeviceType,
          backupset.position,
          backupset.first_lsn,
          backupset.database_backup_lsn,
          backupset.checkpoint_lsn,
          backupset.last_lsn,
          backupset.software_major_version,
          mediaset.software_name AS Software,
          backupset.is_copy_only,
          backupset.last_recovery_fork_guid
        FROM msdb..backupmediafamily AS mediafamily
        JOIN msdb..backupmediaset AS mediaset
          ON mediafamily.media_set_id = mediaset.media_set_id
        JOIN msdb..backupset AS backupset
          ON backupset.media_set_id = mediaset.media_set_id
        WHERE backupset.database_name = '$($db.Name)' $wherecopyonly
        AND (type = '$first' OR type = '$second')
        $DevTypeFilterWhere
        ) AS a
        WHERE a.BackupSetRank = 1
        ORDER BY a.Type;
        "

                }
                $sql = $sql -join "; "
            }
            else {
                if ($Force -eq $true) {
                    $select = "SELECT * "
                }
                else {
                    $select = "
       SELECT
         backupset.database_name AS [Database],
         backupset.user_name AS Username,
         backupset.server_name as [server],
         backupset.backup_start_date AS [Start],
         backupset.backup_finish_date AS [End],
         DATEDIFF(SECOND, backupset.backup_start_date, backupset.backup_finish_date) AS Duration,
         mediafamily.physical_device_name AS Path,
         backupset.$backupSizeColumn AS TotalSize,
         CASE backupset.type
        WHEN 'L' THEN 'Log'
        WHEN 'D' THEN 'Full'
        WHEN 'F' THEN 'File'
        WHEN 'I' THEN 'Differential'
        WHEN 'G' THEN 'Differential File'
        WHEN 'P' THEN 'Partial Full'
        WHEN 'Q' THEN 'Partial Differential'
        ELSE NULL
         END AS Type,
         backupset.media_set_id AS MediaSetId,
         mediafamily.media_family_id as mediafamilyid,
         backupset.backup_set_id as backupsetid,
         CASE mediafamily.device_type
        WHEN 2 THEN 'Disk'
        WHEN 102 THEN 'Permanent Disk Device'
        WHEN 5 THEN 'Tape'
        WHEN 105 THEN 'Permanent Tape Device'
        WHEN 6 THEN 'Pipe'
        WHEN 106 THEN 'Permanent Pipe Device'
        WHEN 7 THEN 'Virtual Device'
        ELSE 'Unknown'
         END AS DeviceType,
         backupset.position,
         backupset.first_lsn,
         backupset.database_backup_lsn,
         backupset.checkpoint_lsn,
         backupset.last_lsn,
         backupset.first_lsn as 'FirstLSN',
         backupset.database_backup_lsn as 'DatabaseBackupLsn',
         backupset.checkpoint_lsn as 'CheckpointLsn',
         backupset.last_lsn as 'Lastlsn',
         backupset.software_major_version,
         mediaset.software_name AS Software,
         backupset.is_copy_only,
         backupset.last_recovery_fork_guid"

                }
                
                $from = " FROM msdb..backupmediafamily mediafamily
        INNER JOIN msdb..backupmediaset mediaset ON mediafamily.media_set_id = mediaset.media_set_id
        INNER JOIN msdb..backupset backupset ON backupset.media_set_id = mediaset.media_set_id"

                if ($Database -or $Since -or $Last -or $LastFull -or $LastLog -or $LastDiff -or $DeviceTypeFilter -or $LastLsn -or $BackupTypeFilter) {
                    $where = " WHERE "
                    write-verbose "setting where"
                }
                
                $wherearray = @()
                
                if ($Database.length -gt 0) {
                    $dblist = $Database -join "','"
                    $wherearray += "database_name IN ('$dblist')"
                }

                if ($true -ne $IncludeCopyOnly) {
                    Write-Verbose "excluding copyonly Ico = $IncludeCopyOnly"
                    $wherearray += "is_copy_only='0'"
                }
                
                if ($Last -or $LastFull -or $LastLog -or $LastDiff) {
                    $tempwhere = $wherearray -join " AND "
                    $wherearray += "type = 'Full' AND mediaset.media_set_id = (select top 1 mediaset.media_set_id $from $tempwhere order by backupset.last_lsn DESC)"
                }
                
                if ($Since -ne $null) {
                    $wherearray += "backupset.backup_finish_date >= '$($Since.ToString("yyyy-MM-ddTHH:mm:ss"))'"
                }
                


                if ($DeviceTypeFilter) {
                    $wherearray += "mediafamily.device_type $DeviceTypeFilterRight"
                }
                if ($BackupTypeFilter) {
                    $wherearray += "backupset.type $BackupTypeFilterRight"
                }
                
                if ($LastLsn) {
                    $wherearray += "backupset.last_lsn > $LastLsn"
                }
                if ($where.length -gt 0) {
                    $wherearray = $wherearray -join " AND "
                    $where = "$where $wherearray"
                }
                
                $sql = "$select $from $where ORDER BY backupset.last_lsn DESC"
            }

            Write-Message -Level Debug -Message $sql
            Write-Message -Level SomewhatVerbose -Message "Executing sql query."
            $results = $server.ConnectionContext.ExecuteWithResults($sql).Tables.Rows | Select-Object * -ExcludeProperty BackupSetRank, RowError, Rowstate, table, itemarray, haserrors
            
            if ($raw) {
                Write-Message -Level SomewhatVerbose -Message "Processing as Raw Output."
                $results | Select-Object *, @{ Name = "FullName"; Expression = { $_.Path } }
                Write-Message -Level SomewhatVerbose -Message "$($results.Count) result sets found."
            }
            else {
                Write-Message -Level SomewhatVerbose -Message "Processing as grouped output."
                $GroupedResults = $results | Group-Object -Property backupsetid
                Write-Message -Level SomewhatVerbose -Message "$($GroupedResults.Count) result-groups found."
                $groupResults = @()
                foreach ($group in $GroupedResults) {
                    
                    $fileSql = "select file_type as FileType, logical_name as LogicalName, physical_name as PhysicalName
        from msdb.dbo.backupfile where backup_set_id='$($Group.group[0].BackupSetID)'"

                    
                    Write-Message -Level Debug -Message "FileSQL: $fileSql"
    
                    $historyObject = New-Object Sqlcollaborative.Dbatools.Database.BackupHistory
                    $historyObject.ComputerName = $server.NetName
                    $historyObject.InstanceName = $server.ServiceName
                    $historyObject.SqlInstance = $server.DomainInstanceName
                    $historyObject.Database = $group.Group[0].Database
                    $historyObject.UserName = $group.Group[0].UserName
                    $historyObject.Start = ($group.Group.Start | Measure-Object -Minimum).Minimum
                    $historyObject.End = ($group.Group.End | Measure-Object -Maximum).Maximum
                    $historyObject.Duration = New-TimeSpan -Seconds ($group.Group.Duration | Measure-Object -Maximum).Maximum
                    $historyObject.Path = $group.Group.Path
                    $historyObject.TotalSize = ($group.Group.TotalSize | Measure-Object -Sum).Sum
                    $historyObject.Type = $group.Group[0].Type
                    $historyObject.BackupSetId = $group.Group[0].BackupSetId
                    $historyObject.DeviceType = $group.Group[0].DeviceType
                    $historyObject.Software = $group.Group[0].Software
                    $historyObject.FullName = $group.Group.Path
                    $historyObject.FileList = $server.ConnectionContext.ExecuteWithResults($fileSql).Tables.Rows
                    $historyObject.Position = $group.Group[0].Position
                    $historyObject.FirstLsn = $group.Group[0].First_LSN
                    $historyObject.DatabaseBackupLsn = $group.Group[0].database_backup_lsn
                    $historyObject.CheckpointLsn = $group.Group[0].checkpoint_lsn
                    $historyObject.LastLsn = $group.Group[0].Last_Lsn
                    $historyObject.SoftwareVersionMajor = $group.Group[0].Software_Major_Version
                    $historyObject.IsCopyOnly = if ($group.Group[0].is_copy_only -eq 1) {
                        $true
                    }
                    else {
                        $false
                    }
                    $HistoryObject.LastRecoveryForkGuid = $group.Group[0].last_recovery_fork_guid
                    $groupResults += $historyObject
                }
                $groupResults | Sort-Object -Property LastLsn, Type
            }
        }
    }
}