internal/functions/Get-BackupAncientHistory.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
function Get-BackupAncientHistory {
    <#
        .SYNOPSIS
            Returns details of the last full backup of a SQL Server 2000 database
 
        .DESCRIPTION
            Backup History command to pull limited history from a SQL 2000 instance. If not using SQL 2000, please use Get-DbaBackupHistory which pulls more infomation, and has more options. This is just here to cope with 2k and copy-DbaDatabase issues
 
        .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 Credential
            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.
 
        .NOTES
        Author: Stuart Moore (@napalmgram), stuart-moore.com
 
        dbatools PowerShell module (https://dbatools.io, clemaire@gmail.com)
        Copyright (C) 2016 Chrissy LeMaire
        License: MIT https://opensource.org/licenses/MIT
 
    #>

    [CmdletBinding(DefaultParameterSetName = "Default")]
    Param (
        [parameter(Mandatory = $true)]
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter]$SqlInstance,
        [Alias("Credential")]
        [PsCredential]$SqlCredential,
        [Alias("Databases")]
        [object[]]$Database,
        [string]$FileNameStub,
        [Alias('Silent')]
        [switch]$EnableException
    )
    BEGIN {
        try {
            Write-Message -Level VeryVerbose -Message "Connecting to $SqlInstance." -Target $SqlInstance
            $server = Connect-SqlInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential
        }
        catch {
            Stop-Function -Message "Failed to process Instance $SqlInstance." -InnerErrorRecord $_ -Target $SqlInstance -Continue
        }
        if ($server.SoftwareVersionMajor -gt 8) {
            Write-Message -Level Warning -Message "This is not the function you're looking for. This is for SQL 2000 only, please use Get-DbaBackupHistory instead. It's much nicer"
        }

        $databases = @()
        if ($null -ne $Database) {
            ForEach ($db in $Database) {
                $databases += [PScustomObject]@{name = $db}
            }
        }
        else {
            $databases = $server.Databases
        }
    }

    PROCESS {
        foreach ($db in $Database) {
            Write-Message -Level Verbose -Message "Processing database $db"
            $sql = "
            SELECT
            a.Server,
             a.[Database],
             a.Username,
             a.Start,
             a.[End],
             a.Duration,
             a.[Path],
             a.Type,
            NULL as 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,
                NULL as is_copy_only,
            NULL as last_recovery_fork_guid
            FROM (
            SELECT
              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,
              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
            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'
                    ) AS a
            where a.backupsetid in (Select max(backup_set_id) from msdb..backupset where database_name='$db')"

            Write-Message -Level Debug -Message $sql
            $results = $server.ConnectionContext.ExecuteWithResults($sql).Tables.Rows | Select-Object * -ExcludeProperty BackupSetRank, RowError, Rowstate, table, itemarray, haserrors
            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.ComputerName
                $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 = $NULL
                $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
                }
                $groupResults += $historyObject
            }
            $groupResults | Sort-Object -Property LastLsn, Type
        }

    }

    END {}
}