Script PowerShell–Check des sauvegardes

Le planning plutôt chargé de ces dernières semaines ne m’a pas permis de poster à ma guise sur ce blog. Malheureusement, l’année 2018 qui se profile risque d’être encore plus chargée … Le premier semestre est d’ores et déjà complet, quant au second, le taux de remplissage frôle déjà les 75%. Mais on ne va pas se plaindre, hein.

J’initie donc aujourd’hui une série de billets concernant PowerShell. Pour être totalement honnête, il m’a fallu un certain temps avant d’adhérer à ce langage. D’une part SQL Server dispose déjà d’un langage de script, pourquoi en utiliser un second ??? Et d’autre part, avant que le PS ne soit disponible, pour des besoins d’administration, je faisais beaucoup de VBScript.

Avec du recul, je regrette presque de ne pas avoir franchi le cap plus tôt ! Bon, cela fait quand même quelques années que je pratique maintenant (à mon niveau bien sûr) et ceux qui me suivant en formation ou lors d’évènements ont pu voir (et récupérer) quelques scripts que j’ai écrits. Soyons clair, ces scripts sont fonctionnels, et ne se targuent pas d’être une référence dans l’art de coder en PowerShell. Je reste tout à fait lucide quant à la marge de progression qui est la mienne dans ce langage.

Pour débuter cette série, je vous propose un script permettant de checker vos sauvegardes rapidement. Le script va lister les instances visibles et se connecter (authentification Windows) et vérifier les dates des dernières sauvegardes. Eh oui, n’oubliez pas qu’un DBA est jugé sur sa capacité à restaurer une base. Alors pas question « d’oublier » des bases …

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

clear-host 

# Can use a predefined list instead ....
$InstanceList = sqlcmd -L
# or
# $InstanceList = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
# or
# $InstanceList = "srv1","srv2"

# excluded instances
$ExcludedHostList = "devsql2016","devsql2017"

$ShowSystemDatabases = $false 
[int]$global:TotalDatabaseStorage = 0
[int]$global:TotalDatabaseCount = 0


$Debug = $false
$ConnectionTimeout = 1

$CheckBackup = $true
$PctLogUsedThreshold = 70
$LastFullBackupAge = -1
$LastDiffBackupAge = -1 # hoping there is at least a full or a diff backup the day before ...

$LastdbccLastKnownGoodAge = -7

$CheckAutoShrink = $true
$CheckAutoClose = $true
$CheckdbccLastKnownGood = $true

$InstanceErrorList = @()
$DisplayInstanceErrorList = $false


function Get-SQLBackup ([object] $Server) {

    Write-host  -ForegroundColor Green "Connecting "$Server.name
    Write-host $Server.Edition " - Version " $Server.VersionString " (" $Server.ProductLevel ") - " $Server.collation

    if (!($Server.ConnectionContext.IsInFixedServerRole("sysadmin"))) { 
        Write-Host $Server.ConnectionContext.TrueLogin " is not sysadmin !" -ForegroundColor Magenta
        $InstanceErrorList += "Missing permissions on $InstanceName"
    }

    $Databases = $server.Databases | Where-Object Status -eq "normal" | sort-object ID


    write-host ""
    $InstanceStorage = 0
    foreach ($Database in $Databases) {
         try {
             $InstanceStorage += $Database.size


             if (!($Server.ConnectionContext.IsInFixedServerRole("sysadmin"))) { 
                continue 
             }



             If (($ShowSystemDatabases) -or ($Database.iD -gt 4)) {
                Write-Host $Database "("$Database.size.ToString("N") "MB ) " -NoNewline

                if ($CheckdbccLastKnownGood) {
                    $LastKnownGood = $($Database.ExecuteWithResults("DBCC DBINFO() WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"}).value #  | Select-Object Value
                    $LastKnownGood = [datetime]::ParseExact($LastKnownGood.split(" ")[0],'yyyy-MM-dd',$null)
                    if ($LastKnownGood -lt (Get-date).AddDays($LastdbccLastKnownGoodAge)) {
                        Write-Host " | CheckDB " $LastKnownGood.ToString("yyyy-MM-dd") " " -NoNewline -ForegroundColor Red
                    }
                    else{
                        Write-Host " | CheckDB " $LastKnownGood.ToString("yyyy-MM-dd") " " -NoNewline 
                    }
                 }
                
                
                if ($Database.AutoShrink -and $CheckAutoShrink) {
                    Write-Host " | AutoShrink " -NoNewline -ForegroundColor Red
                }
                if ($database.AutoClose -and $CheckAutoClose) {
                    Write-Host " | AutoClose " -NoNewline -ForegroundColor Red
                }


                if ($CheckBackup) {

                    $PercentLogUsed =  [math]::round(($Database.LogFiles[0].UsedSpace*100.0/($Database.LogFiles[0].Size)),2)
                    if ($PercentLogUsed -ge $PctLogUsedThreshold) {
                        Write-Host "| Recovery" $database.RecoveryModel "("$database.LogReuseWaitStatus","$PercentLogUsed "% ," $([math]::round(($Database.LogFiles[0].UsedSpace),2)) "KB)"   -NoNewline -ForegroundColor red
                    }
                    else {
                        Write-Host "| Recovery" $database.RecoveryModel "("$database.LogReuseWaitStatus","$PercentLogUsed "% ," $([math]::round(($Database.LogFiles[0].UsedSpace),2)) "KB)"   -NoNewline 
                    }
                     

                    if ($database.LastBackupDate -lt (Get-date).AddDays($LastFullBackupAge)) {
                        $OupsHopeForDiffBackup = $true
                    }
                    else{
                        $OupsHopeForDiffBackup = $false
                    }

               
                    if ($OupsHopeForDiffBackup) {
                        if ($database.LastDifferentialBackupDate -lt (Get-date).AddDays($LastDiffBackupAge)) {
                            $ButThereIsaDiffBackup = $false
                        }
                        else{
                            $ButThereIsaDiffBackup = $true
                        }
                    }

                    if (($OupsHopeForDiffBackup) -and (!($ButThereIsaDiffBackup)) ) {
                        Write-Host " | Last Full" $database.LastBackupDate.ToString("yyyy-MM-dd") -NoNewline -ForegroundColor Red
                        Write-Host " | Last Diff" $database.LastDifferentialBackupDate.ToString("yyyy-MM-dd") -NoNewline -ForegroundColor Red
                    }
                    elseif (($OupsHopeForDiffBackup) -and ($ButThereIsaDiffBackup)) {
                        Write-Host " | Last Full" $database.LastBackupDate.ToString("yyyy-MM-dd") -NoNewline -ForegroundColor Magenta
                        Write-Host " | Last Diff" $database.LastDifferentialBackupDate.ToString("yyyy-MM-dd") -NoNewline
                    }
                    else {
                        Write-Host " | Last Full" $database.LastBackupDate.ToString("yyyy-MM-dd") -NoNewline 
                        if ($database.LastDifferentialBackupDate.Year -ne 1){
                            Write-Host " | Last Diff" $database.LastDifferentialBackupDate.ToString("yyyy-MM-dd") -NoNewline
                        }
                        else {
                            Write-Host " | Last Diff - none - " -NoNewline
                        }
                   
                    }


                    if ($database.RecoveryModel -ne "Simple"){
                        if ( $database.LastLogBackupDate -lt (Get-date).AddHours(-4) ) {
                            Write-Host " | Last Log" $database.LastLogBackupDate.ToString("yyyy-MM-dd hh:mm:ss") -NoNewline -ForegroundColor Red
                        }
                        else
                        {
                            Write-Host " | Last Log" $database.LastLogBackupDate.ToString("yyyy-MM-dd hh:mm:ss") -NoNewline 
                        }
                    }
                    else {
                        Write-Host " | Last Log N/A" -NoNewline
                    }
                }

                write-host ""
             }
        }
        catch {
            
            if ($Debug) {
                #Write-Host "Missing permissions on $server $Database" -ForegroundColor Red
                Write-host -ForegroundColor Red $_.Exception.Message
            }
        }
    }


    Write-Host ""
    write-host $server.Databases.Count "Databases ("$InstanceStorage.ToString("N") "MB )"
    $global:TotalDatabaseStorage = $global:TotalDatabaseStorage + $InstanceStorage   
    $global:TotalDatabaseCount = $global:TotalDatabaseCount  + $server.Databases.Count
}



ForEach ($InstanceName in $InstanceList) {
    $InstanceName = $InstanceName.trim()
    if ($InstanceName -eq "") {continue}
    if ($InstanceName -eq "Servers:") {continue}

    # Check excluded instances
    if ($ExcludedHostList -contains $InstanceName) {
        if ($Debug) {
            write-host "#############################################################################" -ForegroundColor yellow
            Write-Host $InstanceName " excluded" -ForegroundColor yellow
            write-host "_____________________________________________________________________________" -ForegroundColor yellow
            Write-Host " "
        }
        continue
    }
        
    $Server = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($InstanceName)
    $Server.ConnectionContext.ConnectTimeout = $ConnectionTimeout

    if (!($Server.ComputerNamePhysicalNetBIOS)) {
        $InstanceErrorList +="Error connecting $InstanceName"
    }
    else {
        write-host "#############################################################################" -ForegroundColor Green
        Get-SQLBackup $Server
        write-host "_____________________________________________________________________________" -ForegroundColor Green
        Write-Host " "
    }

}


# Display grand total 
if ($global:TotalDatabaseCount -gt 0) {
    write-host ""
    write-host "Grand Total :"
    Write-Host $global:TotalDatabaseCount " Databases ("$global:TotalDatabaseStorage.ToString("N") "MB )" 
}

if ($DisplayInstanceErrorList) {
    write-host ""
    write-host "Errors :"
    $InstanceErrorList
}

Bien entendu je vous encourage à modifier ce bout de code pour qu’il s’adapte à vos attentes … Merci cependant de conserver les crédits …

Happy PowerShell