Powershell : rapport des derniers backups MSSQL

par

Bonjour,

Voici un petit script qui permet d’afficher les DB d’une instance MSSQL, ainsi que le dernier backup effectué pour celles-ci.

Il est possible de planifier l’exécution de ce script afin d’obtenir un rapport quotidien pour vérification.

Adaptez simplement les variables à vos besoin, et c’est partis !

# Variables and functions
$DB_Server = "dbserver.your-domain.com"
$SMTP = "mailserver.your-domain.com"
$to = "you_email@your-domain.com"
$from = "no-reply@your-domain.com"
 
Function Invoke-SQL {
    param(
        [string] $dataSource,
        [string] $database,
        [string] $sqlCommand = $(throw "Please specify a query.")
        )
 
    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"
 
    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()
 
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null
 
    $connection.Close()
    $dataSet.Tables
}

$SQLCommand = "SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name"

$SQLCheck = Invoke-SQL -dataSource $DB_Server -sqlCommand $SQLCommand

[string]$body = $SQLCheck.DefaultView | Select DatabaseName, LastBackUpTime | ConvertTo-Html -Head "<style>table {border-collapse: collapse;padding:5px;}table, th, td {border: 1px solid black;}th{white-space: nowrap;background-color:lightgrey;}</style>" -PreContent "<p>Dear,<br /><br />Please find bellow the list of all databases on the your_instance instance, and their last backup :</p>" -PostContent '<p><br />Regards,<br /><br />Your IT Team</p>'

Send-MailMessage -Body $body -BodyAsHtml -From $from -To $to -Subject "[SQL] Last Database Backups on MSSQLPROD" -Priority High -SmtpServer $smtp

Hope this helps !

Laisser un commentaire