需要监控基本的数据库参数,如恢复模型,页面验证,自动关闭,自动收缩,数据库所有者,自动创建统计启用,数据库创建日期等。因为是windows系统需要接住poweshell。
我们的要求是,所有数据库都应该处于完全恢复模式,并且应该被监控。
此外,我添加了其他重要参数,如AutoShrink, AutoClose等。
脚本效果输出如下:
#Change value of following variables as needed
$ServerList = Get-Content "D:SCMSSQLServer.txt"
$OutputFile = "D:SCMSSQLRecoverModel_MDA_$((Get-Date).ToString('ddMMyyyy_hhmm')).htm"
$HTML = '<style type="text/css">
table{font-family: Calibri,Candara,Segoe,Segoe UI,Optima,Arial,sans-serif;width:100%; border: black; border-style: double; border-width: 2px;}
table td,table th{font-size:1em;border:1px solid #98bf21;padding:3px 7px 2px;}
table th{font-size:1.1em;padding-top:5px;padding-bottom:4px;background-color:#81BEF7;color:#fff}
</Style>'
################################################################################# Author: Sandeep Charaya : http://ilearnsql.wordpress.com/# Date: 16.06.2020# Comment: Script to obtain the disk space on remote servers################################################################################
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>
<TR>
<TH align=center><B>Date</B></TH>
<TH align=center><B>DatabaseName</B></TH>
<TH align=center><B>RecoveryModel</B></TH>
<TH align=center><B>PageVerify</B></TH>
<TH align=center><B>AutoClose</B></TH>
<TH align=center><B>AutoShrink</B></TH>
<TH align=center><B>DatabaseOwner</B></TH>
<TH align=center><B>AutoCreateStatisticsEnabled</B></TH>
<TH align=center><B>DB CreateDate</B></TH>
</TR>"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
ForEach ($ServerName in $ServerList)
{
$HTML += "<TR bgColor='#81F7D8'><TD colspan=9 align=center><B>$ServerName</B></TD></TR>"
$SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
Foreach($Database in $SQLServer.Databases | where-object {$_.Name -ne "tempdb" -and $_.Name -ne "master" -and $_.Name -ne "ReportServerTempDB" -and $_.Name -ne "ReportServer$SGSI2W0256TempDB" -and $_.Name -ne "ReportServer$SGSI2W0256"})
{
$DatabaseName=$Database.Name
$SimpleRecoveryModel=$Database.RecoveryModel
$PageVerify=$Database.pageverify
$AutoClose=$Database.autoclose
$Autoshrink=$Database.autoshrink
$DBOwner=$Database.owner
$AutoCreateStatisticsEnabled=$Database.AutoCreateStatisticsEnabled
$CreateDate=$Database.CreateDate
$EndDate=Get-Date
IF ($SimpleRecoveryModel -eq 'simple')
{
$color1 = 'red'
}
ELSE {$color1 = 'Green'}
IF ($PageVerify -ne 'Checksum')
{
$color2 = 'red'
}
ELSE {$color2 = 'Green'}
IF ($AutoClose -eq 'True')
{
$color3 = 'red'
}
ELSE {$color3 = 'Green'}
IF ($Autoshrink -eq 'True')
{
$color4 = 'red'
}
ELSE {$color4 = 'Green'}
IF ($DBOwner -eq "" -Or $DBOwner -Like "*sadm*")
{
$color5 = 'yellow'
}
ELSE {$color5 = 'Green'}
IF ($AutoCreateStatisticsEnabled -eq 'True')
{
$color6 = 'green'
}
ELSE {$color6 = 'red'}
IF ( $color1 -eq 'red' -or $color2 -eq 'red' -or $color3 -eq 'red' -or $color4 -eq 'red' -or $color5 -eq 'red' -or $color6 -eq 'red')
{
$HTML += "<TR>
<TD>$(get-date)</TD>
<TD>$($Database.Name)</TD>
<TD BGCOLOR='$color1' ALIGN=CENTER>$($Database.RecoveryModel)</TD>
<TD BGCOLOR='$color2' ALIGN=CENTER>$($Database.pageverify)</TD>
<TD BGCOLOR='$color3' ALIGN=CENTER>$($Database.autoclose)</TD>
<TD BGCOLOR='$color4' ALIGN=CENTER>$($Database.autoshrink)</TD>
<TD BGCOLOR='$color5' ALIGN=CENTER>$($Database.owner)</TD>
<TD BGCOLOR='$color6' ALIGN=CENTER>$($Database.AutoCreateStatisticsEnabled)</TD>
<TD BGCOLOR='green' ALIGN=CENTER>$($Database.createdate)</TD>
</TR>"
}
}
}
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile
adm_d14_jxpp_wx_robot_group_task_send_msg_di