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 !