Powershell : rapport des derniers backups MSSQL

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

# 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 !

L	M	M	J	V	S	D
					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

Laisser un commentaire Annuler la réponse