So, if you’ve read “Automated SQL backup testing – Part 1” and built yourself some arrays containing SQL backup locations and file names this post will show you what to do with them.

For starters you’ll need to connect to SQL, so here’s one way of doing that. I have some code that will return a single value from a query, there are two parts; the function to run and return the result of a query and the code necessary to setup the sql connection.

Lets first setup some variables we shall need for setting up the connection;

Some variables we shall need for setting up or changing the connection:

$PrimarySqlInstance = “SQLServer\InstanceName”
$Database = “master”
$UserID = “UserName”
$SqlPassword = “UserPassword”
$ConnectionTimeout = 0
$QueryTimeout = 0

The code for the connection:

$Connection = new-object system.data.sqlclient.sqlconnection
$ConnectionString = “Server={0};Database={1};User ID={2};Password={3};Connect Timeout={4}” -f $PrimarySqlInstance,$Database,$UserID,$SqlPassword,$ConnectionTimeout
$Connection.connectionString = $ConnectionString
$Connection.open()
$SqlCmd = new-object system.data.sqlClient.sqlCommand($Query,$Connection)
$SqlCmd.CommandTimeout = $QueryTimeout

This gives us the necessary connection to a SQL server with the $connection object, and sets up an object to pass commands to ($SqlCmd). What follows is a little bit of code to pass a query to that tries to return a result if it gets one.

Function NewQuery($QueryString)
{
     try
     {
          $SqlCmd.CommandText = $QueryString
          $answer = $SqlCmd.ExecuteScalar()
          return $answer
     }
     catch
     {
          Write-Host $error[0]
          Write-Host “Error calling $QueryString using $Connection.ConnectionString, does not necessarily imply failure (exception: non-result returning queries or query timeout)”
     }
}

with this code we can loop through our arrays and restore our databases

$counter = 0
write-host “restoring databases”
foreach ($db in $dbname){
     $path = $dbpath[$counter]
     write-host $db
     $test = NewQuery(“RESTORE DATABASE $db FROM DISK = N’$path’ WITH NOUNLOAD”)
     $counter = $counter +1
}

Assuming you have setup this script to have the same paths as visible to the SQL server when run the code above will loop through our arrays and restore them one by one. The query timeout setup on our connection is 5 minutes so depending on database size that may not be long enough however you wouldn’t want to wait on all your databases to restore sequentially either. So we query the SQL server to see if there are any databases marked as restoring.

$restorestatus = “RESTORING”
write-host “waiting for restores to finish”
WHILE ($restorestatus -eq “RESTORING”){
     start-sleep -s 60
     $restorestatus = NewQuery(“select TOP 1 state_desc from sys.databases where state_desc = ‘RESTORING'”)
}

Once all your databases have finished restoring we can begin running DBCC checks (for some basic checking). I chose to check using a maintenance plan. This was quite tricky for me as I had to figure out the SQL to run and check a maintenance plan, which isn’t as easy as you’d think! You need to find the maintenance plan ID for the maintenance plan you wish to run. You can view them by running sp_help_maintenance_plan

$test = NewQuery(“exec msdb.dbo.sp_maintplan_start @plan_id = N’MaintenancePlanID'”)

this will run a maintenance plan with the plan_id you specify. To return a single field to indicate the running state of your plan, run;

$jobstatus = 0
WHILE ($jobstatus -ne 4){
     start-sleep -s 60
     $jobstatus = NewQuery(“select current_execution_status FROM openrowset(‘SQLNCLI’,’Server=uklon-relrst01v\relativity;     trusted_Connection=yes;’,’EXEC msdb..sp_help_job’) WHERE name = N’MaintenanceSubplanName'”)
}

Jobstatus 4 is complete, so once you get that you can run whatever other checks you like, depending on the structures of your databases you could potentially query different fields and store them in an array to check after the script is done to verify if text has become corrupted etc.

With all this code you’ll be able to put something together yourself or you can copy and use the sample script I’ve attached.

SQL testing script (zip)