Mssql dump en powershell

De BlaxWiki
Aller à la navigationAller à la recherche

Informations

  • Ce script permet le backup des bases MSSQL, la copie sur un autre serveur et la restauration sur ce serveur.

Pour exécuter le script : 

    ouvrir un prompt powershell et lancer : C:\Agarik\Scripts\backup_copie_restau.ps1 avec les paramètres souhaités 

Liste des paramètres (case sensitive!) :
    -checkOnly : vérifie que les bases ont bien été restaurées sur preprod1 et renvoie le résultat dans la supervision
    -copyOnly : copie les bases sur le serveur de preprod
    -restauOnly : restaure les bases sur preprod
    -doAll : fait toute les actions (attention aux backup des bases après 1h !)
    -yesterday : permet de copier/restaurer les bases d'hier si on a dépassé minuit (à ajouter en plus des autres params)
    -green : renvoie du vert même en cas d'erreur 

Exemple : en cas de problème lors de la copie sur : il faudra relancer le script avec les options -copyOnly -restauOnly ainsi que -checkOnly pour mettre à jour la supervision

Exemple 2 : en cas de problème lors de la restauration après minuit il faut utiliser -restauOnly -yesterday -checkOnly

Script

<#
.SYNOPSIS
Script permettant le backup des bases MSSQL, la copie sur un autre serveur et la restauration sur ce serveur.
.EXAMPLE
#>
[CmdletBinding()]
Param(
    [switch]$doAll,
    [switch]$copyOnly,
    [switch]$restauOnly,
    [switch]$checkOnly,
    [switch]$green,
    [switch]$yesterday)




## Pour désactiver la restauration
#$disableRestore=$true
$disableRestore=$false

Add-PSSnapin *sql*
Import-Module c:\Agarik\Scripts\PSLogging

$excludedDatabases="Next_Test","Aval_Test","Aval_Test_pdf","tempdb"
$systemDatabases="master","model","msdb","tempdb"
$backupServer="prod1b"
$backupPath="D:\Backup\CURRENT"
$backupPath2="d$\Backup\CURRENT"
$ignoreRecentBackup="6" # ne pas faire de backup si le derneir backup a été fait moins de 6h auparavant
$restoreServer="preprod1b"
$restorePath="D:\Backups_from_prod"
$restorePath2="D$\Backups_from_prod" # via smb
$logDir="C:\Agarik\Scripts\Logs"


$todaydate=Get-Date -Format yyyyMMdd
$logFile = Add-LogFile -Path $logDir\backup-$todaydate.log
$global:outputMon=""
$global:gErrorNumber=0
$startLocation=Get-Location


function Send-Supervision() {
        $server="prod1b.alphavalue.eu"
        $service="backup"
        $supervision="10.252.15.235"
        $lifetime=1500
        $vc="C:\Agarik\Vision\bin\vc_send.exe"
        if (($gerrorNumber -gt 0) -and (!$green)) {
            $color="red"
        }
        else {
            $color="green"
        }
        & $vc $supervision $server $service $color $lifetime $outputMon
}
function Append-Supervision($msg) {
    Write-Output $msg
    $global:outputMon+=$msg+"`n"
}

function BackupAllDatabases {
    $ErrorNumber=0
    try {
        $databases=Get-ChildItem -Path sqlserver:\sql\$backupServer\default\databases -Force -ErrorAction Stop
    }
    catch {
        $ErrorNumber+=1
        Append-Supervision "     &red $($db.Name) : Impossible de lister les bases : ERREUR : $_"
    }
    Append-Supervision "Backup des bases :"
    
    foreach ($db in $databases) {
        $dbname=$db.Name
        $bakfile="$backupPath\$($dbname)_$todaydate.bak"
        if (($db.lastBackupDate -gt (Get-Date).addHours(-$ignoreRecentBackup)) -and (Test-Path -Path $bakfile) ) {
            Append-Supervision "     &clear $($db.Name) : Ignore, dernier backup date de moins de $ignoreRecentBackup heures"
        }
        elseif ($excludedDatabases -contains $db.name) {
            Append-Supervision "     &clear $($db.Name) : exclue sur demande client"
        }
        else {
            try {
                Set-Location SQLSERVER:\SQL\$backupServer\DEFAULT\
                Invoke-Sqlcmd -verbose -OutputSqlErrors $True -QueryTimeout 7200 -ErrorAction Stop -Query "BACKUP DATABASE $dbname TO DISK=N'$bakfile' WITH INIT;"
                Append-Supervision "     &green $($db.Name) : Backup OK"
            }
            catch {
                $ErrorNumber+=1
                Append-Supervision "     &red $($db.Name) : Backup ERREUR : $_"
            }
        }
    }
    if ($ErrorNumber -gt 0) {
        Append-Supervision "&red Backup des bases : $ErrorNumber erreurs !"
    } else {
        Append-Supervision "&green Backup des bases : OK !"
    }
    $global:gErrorNumber+=$ErrorNumber
}

function CleanOldBackup {
    $oldfiles=Get-ChildItem -Path $backupPath | Where-Object {$_.LastWriteTime -lt (Get-Date).addHours(-22)}
    Append-Supervision "Suppression des anciens backups :"
    foreach ($file in $oldfiles) {
        try {
            $file | Remove-Item -ErrorAction Stop
            Append-Supervision "     &green $($file.Name) : Fichier supprimé"
        }
        catch {
            $ErrorNumber+=1
            Append-Supervision ="     &red $($file.Name) : ERREUR : $_"
        }
    }
    if ($ErrorNumber -gt 0) {
        Append-Supervision "&red Suppression anciens backups : $ErrorNumber erreurs !"
    } else {
        Append-Supervision "&green Suppression anciens backups : OK !"
    }
    $global:gErrorNumber+=$ErrorNumber
}

function CopyToOtherServer {
    Set-Location SQLSERVER:\SQL\$backupServer\DEFAULT\
    Append-Supervision "Copie des bases du $todaydate sur $restoreServer :"
    try {
        $databases=Get-ChildItem -Path sqlserver:\sql\$backupServer\default\databases -Force -ErrorAction Stop
    }
    catch {
        $ErrorNumber+=1
        Append-Supervision "     &red $($db.Name) : Impossible de lister les bases : ERREUR : $_"
    }
    foreach ($db in $databases) {
        if ($excludedDatabases -contains $db.name) {
            Append-Supervision "     &clear $($db.Name) : exclue sur demande client"
        }
        else {
            try {
                Set-Location $startLocation
                Copy-Item -Force -Path "\\$backupServer\$backupPath2\$($db.name)_$todaydate.bak" "\\$RestoreServer\$restorePath2" -ErrorAction Stop
                Append-Supervision "     &green $($file.Name) : Fichier $backupPath\$($db.name)_$todaydate.bak transféré"
            }
            catch {
                $ErrorNumber+=1
                Append-Supervision "     &red $($file.Name) : ERREUR $backupPath\$($db.name)_$todaydate.bak : $_"
            }
        }
    }
    if ($ErrorNumber -gt 0) {
        Append-Supervision "&red Transfert des fichiers : $ErrorNumber erreurs !"
    } else {
        Append-Supervision "&green Transfert des fichiers : OK !"
    }
    $global:gErrorNumber+=$ErrorNumber
}

function RestoreOnOtherServer {

    $ErrorNumber=0
    try {
        $databases=Get-ChildItem -Path sqlserver:\sql\$backupServer\default\databases -ErrorAction Stop
    }
    catch {
        $ErrorNumber+=1
        Append-Supervision "     &red $($db.Name) : Impossible de lister les bases : ERREUR : $_"
    }
    Append-Supervision "Restauration des bases :"
    
    foreach ($db in $databases) {
        if ($systemDatabases -contains $db.name) {
            Append-Supervision "     &clear $($db.Name) : exclue, base systeme"
        }
        elseif ($excludedDatabases -contains $db.name) {
            Append-Supervision "     &clear $($db.Name) : exclue sur demande client"
        }
        else {
            try {
                $dbname=$db.Name
                $bakfile="$restorePath\$($dbname)_$todaydate.bak"
                Set-Location SQLSERVER:\SQL\$restoreServer\DEFAULT\
                Invoke-Sqlcmd -verbose -OutputSqlErrors $True -ErrorAction Stop -QueryTimeout 7200 -Query "ALTER DATABASE $dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;RESTORE DATABASE $dbname FROM DISK=N'$bakfile' WITH FILE=1;ALTER DATABASE $dbname SET MULTI_USER;"
                Append-Supervision "     &green $($db.Name) : Restore OK"
            }
            catch {
                $ErrorNumber+=1
                Append-Supervision "     &red $($db.Name) : Restore ERREUR : $_"
            }
        }
    }
    if ($ErrorNumber -gt 0) {
        Append-Supervision "&red Restauration des bases : $ErrorNumber erreurs !"
    } else {
        Append-Supervision "&green Restauration des bases : OK !"
    }
    $global:gErrorNumber+=$ErrorNumber

}
function CheckRestore {
    $ErrorNumber=0
    try {
        $databases=Get-ChildItem -Path sqlserver:\sql\$restoreServer\default\databases -ErrorAction Stop
    }
    catch {
        $ErrorNumber+=1
        Append-Supervision "     &red $($db.Name) : Impossible de lister les bases : ERREUR : $_"
    }
    Append-Supervision "Check des dates de restauration :"
    
    foreach ($db in $databases) {
        if ($systemDatabases -contains $db.name) {
            Append-Supervision "     &clear $($db.Name) : exclue, base systeme"
        }
        elseif ($excludedDatabases -contains $db.name) {
            Append-Supervision "     &clear $($db.Name) : exclue sur demande client"
        }
        else {
            try {
                $dbname=$db.Name
                Set-Location SQLSERVER:\SQL\$restoreServer\DEFAULT\
                $result=Invoke-Sqlcmd -verbose -OutputSqlErrors $True -ErrorAction Stop -Query "WITH restore_date_cte   AS ( SELECT   d.name 
                                                                                                                , rh.restore_date
                                                                                                                , bs.backup_finish_date
                                                                                                                , ROW_NUMBER() OVER --get the most recent
                                                                                                                  ( PARTITION BY d.name ORDER BY rh.restore_date DESC ) AS RestoreOrder
                                                                                                         FROM     sys.databases AS d
                                                                                                                  LEFT JOIN msdb.dbo.restorehistory AS rh
                                                                                                                      ON d.name = rh.destination_database_name
                                                                                                                  LEFT JOIN msdb.dbo.BackupSet AS bs
                                                                                                                      ON rh.backup_set_id = bs.backup_set_id
                                                                                                         WHERE d.name = '$dbname'
                                                                                                       )
                                                                                              SELECT  rdc.name
                                                                                                    , rdc.restore_date
                                                                                                    , rdc.backup_finish_date
                                                                                                    , rdc.RestoreOrder
                                                                                              FROM    restore_date_cte AS rdc
                                                                                              WHERE   RestoreOrder = 1;;"
                if ($result.backup_finish_date -lt (Get-Date).AddHours(-22)) {
                    $ErrorNumber+=1
                    Append-Supervision "     &red $dbname : Le dernier backup restauré date d'il y a plus d'un jour : backup du $($result.backup_finish_date) restauré le $($result.restore_date)"
                }
                else {
                   Append-Supervision "     &green $dbname : OK : backup $($result.backup_finish_date) Restau $($result.restore_date)"
                 }
            }
            catch {
                $ErrorNumber+=1
                Append-Supervision "&red $($db.Name) : Impossible de vérifier les dates de restauration ERREUR : $_"
            }
        }
    }
    if ($ErrorNumber -gt 0) {
        Append-Supervision "&red Verification restauration des bases : $ErrorNumber erreurs !"
    } else {
        Append-Supervision "&green Verification restauration des bases : OK !"
    }
    $global:gErrorNumber+=$ErrorNumber

}
function CleanOldRemoteBackup {
    Set-Location $startLocation
    $oldfiles=Get-ChildItem -Path \\$RestoreServer\$restorePath2 | Where-Object {$_.LastWriteTime -lt (Get-Date).addHours(-22)}
    Append-Supervision "Suppression des anciens backups sur le serveur distant:"
    foreach ($file in $oldfiles) {
        try {
            $file | Remove-Item -ErrorAction Stop
            Append-Supervision "     &green $($file.Name) : Fichier supprimé"
        }
        catch {
            $ErrorNumber+=1
            Append-Supervision ="     &red $($file.Name) : ERREUR : $_"
        }
    }
    if ($ErrorNumber -gt 0) {
        Append-Supervision "&red Suppression anciens backups : $ErrorNumber erreurs !"
    } else {
        Append-Supervision "&green Suppression anciens backups : OK !"
    }
    $global:gErrorNumber+=$ErrorNumber
}




if ($doAll) {
    BackupAllDatabases
    if ($gerrorNumber -eq 0) {
        CleanOldBackup
        CopyToOtherServer
        CleanOldRemoteBackup
    }
    if (($gerrorNumber -eq 0) -and (!$disableRestore)) {
        RestoreOnOtherServer
        CheckRestore
    }
    Send-Supervision
}
else {
    if ($copyOnly) {
        if ($yesterday) { $todaydate=(get-date).AddDays(-1) | Get-Date -Format yyyyMMdd }
        CopyToOtherServer
    }
    if ($restauOnly) {
        if ($yesterday) { $todaydate=(get-date).AddDays(-1) | Get-Date -Format yyyyMMdd }
        if (!$disableRestore) { RestoreOnOtherServer }
    }
    if ($checkOnly) {
        CheckRestore
        Send-Supervision
    }

}
if (!($doAll -or $copyOnly -or $restauOnly -or $checkOnly -or $clean)) {
    Write-output "Merci d'utiliser les options"
}

set-location $startLocation
$logFile | Disable-LogFile
Remove-PSSnapin *sql*
Récupérée de « https://wiki.blaxeen.com/index.php?title=Mssql_dump_en_powershell&oldid=3774 »