Mssql dump en powershell

Script 1

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*

Script 2

Script

On peut mettre en argument : "-Complet -Toutes" ou "-Transactions -Toutes"

[CmdletBinding()]
Param(
	[String]$BDD,
	[switch]$Toutes,
	[switch]$Complet,
	[switch]$Transactions,
	[switch]$Green
)

## CONF SUPERVISION
$Serveur = "fbd-sql-01.fbd"
$Service = "backup"
$Supervision = "10.252.15.235"
$DureeSupervision = 180
$VisionClient = "C:\Agarik\Vision\bin\vc_send.exe"
$Erreurs = 0

## CONF BACKUP
$Instance = "$env:computername\default"
$RepSauvegarde = 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup'
$Retention = '-1' ## Nombre de jours
$Compression = 'On'

## IGNORES
# Listes de bases à ne pas sauvegarder séparées par des ,
# Ex :  $IgnoreComplet = 'DB1','DB2','DB3'
$IgnoreComplet = ''
$IgnoreTransactions = 'master','msdb'

## MODULE MSSQL
#Add-PSSnapin *SQL*
Import-Module Sqlps
#Import-Module SqlServer

## LOCK FILE
If (-Not (Test-Path $RepSauvegarde\backup.lock)){
	New-Item -Type File $RepSauvegarde\backup.lock
}
ElseIf ($Complet){
	Write-Host 'Une sauvegarde est déjà en cours.`nAttente de la fin de la sauvegarde.'
	While (-Not (Test-Path $RepSauvegarde\backup.lock)){
		Start-Sleep 10
	}
}
Else{
	Write-Host 'Un backup est déjà en cours.'
	Exit
}

## DATE/HEURE DU BACKUP
$Date = Get-Date -Format 'yyyyMMdd'
$Heure = Get-Date -Format 'HHmmss'

## CONNEXION
try {
	$AllBases = Get-ChildItem -Path sqlserver:\sql\$Instance\databases -Force -ErrorAction Stop | Where-Object {$_.Name -Ne 'tempdb' }
	$Message = '&green Connexion au serveur SQL :  OK'
}
catch {
	$Erreurs += 1
	$Message = '&red Connexion au serveur SQL :  NOK !'
}
If ( -Not $Toutes -And $BDD){
	$Bases = "$BDD"
}
Else {
	$Bases = $AllBases.Name
}

## CREATION TABLEAU SUPERVISION
$Status = New-Object System.Data.DataTable "SQL Server Backups"
$Status.Columns.Add((New-Object System.Data.DataColumn Base,([string])))
$Status.Columns.Add((New-Object System.Data.DataColumn Type,([string])))
$Status.Columns.Add((New-Object System.Data.DataColumn Date,([string])))
$Status.Columns.Add((New-Object System.Data.DataColumn Resultat,([string])))
ForEach ($Base in $AllBases.Name){
		$Ligne = $Status.NewRow()
		$Ligne.Base = $Base
		$Ligne.Type = 'Complet'
		$Ligne.Date = ''
		$Ligne.Resultat = 0
		$Status.Rows.Add($Ligne)
		$Ligne = $Status.NewRow()
		$Ligne.Base = $Base
		$Ligne.Type = 'Transactions'
		$Ligne.Date = ''
		$Ligne.Resultat = 'OK'
		$Status.Rows.Add($Ligne)
}

## IMPORT XML
If (Test-Path "$RepSauvegarde\status.xml"){
	$Xml = Import-Clixml -Path "$RepSauvegarde\status.xml"
	ForEach ($Ligne in $Xml){
		if ($AllBases.Name.Contains($Ligne.Base)){
			($Status | Where-Object {($_.Base -Eq $Ligne.Base -And $_.Type -Eq $Ligne.Type)}).Date = $Ligne.Date
			($Status | Where-Object {($_.Base -Eq $Ligne.Base -And $_.Type -Eq $Ligne.Type)}).Resultat = $Ligne.Resultat
		}
	}
}

## BACKUP FULL
If ($Erreurs -Eq 0 -And $Complet){
	ForEach ($Base in $Bases){
		If ($IgnoreComplet.Contains($Base)){
			($Status | Where-Object {($_.Base -Eq "$Base" -And $_.Type -Eq 'Complet')}).Resultat = 'Ignore'
		}
		Else{
			try {
				Backup-SqlDatabase -Path "sqlserver:\sql\$Instance\databases" -Database "$Base" -BackupFile "${RepSauvegarde}\${Base}_${Date}_full.bak" -CompressionOption "$Compression"
				($Status | Where-Object {($_.Base -Eq "$Base" -And $_.Type -Eq 'Complet')}).Resultat = 'OK'
				
			}
			catch {
				$Erreurs += 1
				($Status | Where-Object {($_.Base -Eq "$Base" -And $_.Type -Eq 'Complet')}).Resultat = "$_"
			}
			($Status | Where-Object {($_.Base -Eq "$Base" -And $_.Type -Eq 'Complet')}).Date = Get-Date -Format 'dd/MM/yyyy-HH:mm:ss'
			If (($Status | Where-Object {($_.Base -Eq "$Base" -And $_.Type -Eq 'Complet')}).Resultat -Eq 'OK'){
				Remove-Item "${RepSauvegarde}\${Base}_*_logs.trn"
			}
		}
	}
}
ElseIf ($Erreurs -Eq 0 -And $Transactions){
## BACKUP TRANSACTIONS
	ForEach ($Base in $Bases){
		If ($IgnoreTransactions.Contains($Base)){
			($Status | Where-Object {($_.Base -Eq "$Base" -And $_.Type -Eq 'Transactions')}).Resultat = 'Ignore'
		}
		Else{
			try {
				Backup-SqlDatabase -Path "sqlserver:\sql\$Instance\databases" -Database "$Base" -BackupFile "${RepSauvegarde}\${Base}_${Date}_${Heure}_logs.trn" -CompressionOption "$Compression" -BackupAction Log
				($Status | Where-Object {($_.Base -Eq "$Base" -And $_.Type -Eq 'Transactions')}).Resultat = 'OK'
			}
			catch {
				$Erreurs += 1
				($Status | Where-Object {($_.Base -Eq "$Base" -And $_.Type -Eq 'Transactions')}).Resultat = "$_"
			}
			($Status | Where-Object {($_.Base -Eq "$Base" -And $_.Type -Eq 'Transactions')}).Date = Get-Date -Format 'dd/MM/yyyy-HH:mm:ss'
		}
	}
}

## SUPPRESSION DES ANCIENS BACKUPS
Get-ChildItem $RepSauvegarde | Where-Object { $_.LastWriteTime -Lt ((Get-Date).AddDays($Retention)) } | Remove-Item

## VERIFICATION
$Status = $Status | Sort-Object -Property @{Expression = "Type"; Descending = $False}, @{Expression = "Base"; Descending = $False}
If ($Status.Count -Gt '0'){
	$Status | Export-Clixml -Path "$RepSauvegarde\status.xml"
}
$Message = "$Message`n`nSauvegarde complète des bases :"
ForEach ($Ligne in ($Status | Where-Object {$_.Type -Eq 'Complet'} | Sort-Object -Property 'Base')){
	$Base = $Ligne.Base.ToString()
	$Date = $Ligne.Date.ToString()
	$Resultat = $Ligne.Resultat.ToString()
	If ($Resultat -Eq 'OK'){
		$Message = "$Message`n  &green $Base $Date : OK"
	}
	ElseIf ($Resultat -Eq 'Ignore'){
		$Message = "$Message`n  &clear $Base $Date : Ignore"
	}
	Else{
		$Erreurs += 1
		$Message = "$Message`n  &red $Base $Date : $Resultat"
	}
}
$Message="$Message`n`nSauvegarde des journaux de transactions :"
ForEach ($Ligne in ($Status | Where-Object {$_.Type -Eq 'Transactions'}  | Sort-Object -Property 'Base')){
	$Base = $Ligne.Base.ToString()
	$Date = $Ligne.Date.ToString()
	$Resultat = $Ligne.Resultat.ToString()
	If ($Ligne.Resultat -Eq 'OK'){
		$Message = "$Message`n  &green $Base $Date : OK"
	}
	ElseIf ($Resultat -Eq 'Ignore'){
		$Message = "$Message`n  &clear $Base $Date : Ignore"
	}
	Else{
		$Erreurs += 1
		$Message = "$Message`n  &red $Base $Date : $Resultat"
	}
}
$Message="$Message`n`nNombre d'erreurs : $Erreurs"

## RETRAIT DU LOCK
Remove-Item $RepSauvegarde\backup.lock

## ENVOIE DE LA SUPERVISION
if ($Erreurs -Eq 0 -Or $Green){
	$Couleur = 'green'
}
else {
	$Couleur = 'red'
}
& $VisionClient $Supervision $Serveur $Service $Couleur $DureeSupervision $Message

Debug

Si on a ce genre de message d erreurs : " L'instruction 'BACKUP LOG' n'est pas autorisée lorsque le modèle de récupération est SIMPLE. Faites appel à BACKUP DATABASE ou modifiez le modèle de récupération au moyen de ALTER DATABASE.", il faut passer le modèle de récupération à FULL.

SELECT name, recovery_model_desc  
   FROM sys.databases  
      WHERE name = 'TAL_ESKR_PROD' ; Résultat FULL

Modification des bases pour les mettre en mode "récupération à FULL" :
USE [master] ;  
ALTER DATABASE [INV_ACTM_PROD] SET RECOVERY FULL ;