WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Monday, June 13, 2011

Trust but Verify (changing the Page_Verify to checksum)

Just a quick one, if you manage lots of servers and have noticed that the PAGE_VERIFY flag is set to NONE or TORN PAGE DETECTION and want to switch it to the generally accepted proper setting "CHECKSUM".  Here is a script to do it.  Just add the names of your SQL Servers to the $sqlInstanceList and hit the button. (Test it first, it's just something I whipped up today and tested quickly on a few servers).  Also, be sure you understand what you are changing, it makes sense in my environment, but may not in yours. 


[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlInstanceList = ("SQLServerName","SQLServerName2")
   
    foreach($sqlInstance in $sqlInstanceList)
    {
        $sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $sqlInstance
   
        if($sqlServer.VersionMajor -ge 10){
            foreach($db in $sqlServer.Databases | Where-Object{$_.PageVerify -ne "Checksum"}){
                $db.Name
                $db.PageVerify = "Checksum";
                $db.Alter();
            }
        }   
    }

No comments:

Post a Comment