Post disaster recovery SQL Server database checkout script

Question:Post disaster recovery SQL Server database checkout script is required.I need to iterate through hundreds of SQL Server Instances with thousands of databases and return in a readable format the status and if there is any database corruption?

I want this script to run fromm one server , work on the assumption the security context of the user has sufficient privileges to query the servers.

Answer:When there is a full site failover being able to complete a quick review of SQL Server databases is part of the DBA role. The idea is to query the SQL Server Instances and return sufficient information to establish the current situation.

Based on this information it may be necessary to take certain troubleshooting steps and database recoveries

When you're dealing with a few servers this can be a fairly manual process - but if you're dealing with thousands of databases across hundreds of servers - a manual process will not cut the mustard.

A DR checkout process needs to be robust. Networks, storage, virtual \ physical servers, domain controllers are all key checkouts which hopefully has all been documented and tested regularly.

Eventually the DBAs will be requested to checkout the SQL Server environment. I've got multiple scripts used to checkout connectivity, server state.

This Powershell script iterates through a list of sql server instances and queries the SQL Server databases returning state and querying the msdb..suspect pages.

I will also post a script to run before this step to confirm the servers are available and the SQL Services are running.

$isodate=Get-Date -format s 
  $isodate=$isodate -replace(":","")
  $basepath=(Get-Location -PSProvider FileSystem).ProviderPath
  $outputfile="\logs\connectivity_quick_" + $isodate + ".html"
  $outputfilefull = $basepath + $outputfile

 $dt = new-object "System.Data.DataTable"
 foreach ($svr in get-content "C:\SQL_Server\config\instances_all.txt"){
    $svr
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "select db1.name as db_name,@@servername as server_name,db1.state_desc,
CASE WHEN  db2.event_type IS NULL THEN 000 ELSE db2.event_type END as corruption ,ISNULL(error_count,0),getdate() as date_time from 
sys.databases db1
LEFT JOIN msdb..suspect_pages db2 ON db1.database_id = db2.database_id 


 "
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
  
 }

$dt| select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray |ConvertTo-Html -body "Databases checkout"| Add-Content $outputfilefull 

As part of a DR process it is necessary to think about maintaining a list of current servers available. You should work on the assumption that you will not have access immediately to all the DBA management servers - so consider maintain these scripts and server lists in strategic locations.

Strategic locations could include an external server , laptop, mobile storage device etc