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)

I’ve been set the task of restoring and testing all sql backups for a customer application recently and I thought I’d share my experiences with automating the process as opposed to manually restoring hundreds of databases by hand ( which I did and rather quickly got fed up of it, if necessity is the mother of invention; frustration must be the father or uncle ;p )

Straight to the code.

for starters you will need to generate a list of all of your database backups and paths in order to restore them, this can be achieved using the following (assuming your backups are all neatly within the current folder structure and end in .bak)

$SQLBackups = get-childitem -recurse -include *.bak | sort-object

this will produce an array of filesystem objects that can be manipulated to get the information we need. Take note this will look in all subfolders of the current directory to find files ending in .bak and afterwards sort them. The sort is needed as generally your SQL backups will be dated chronologically, having them in order will allow you to select the one(s) you need

$SQLBackups will then look similar to the following;

 

…And repeat depending on the number of databases and the retention of your backups.

Now that we have our filesystem objects we can run through them and pull out the parts we need. Firstly lets setup some variables for storing the information and checking filenames

$Filename = “” # used to store current filename while going through the loop
$dbname = @() # array to hold the database names
$dbpath = @() # array to hold the database paths
$newdb = “no” # whether the database name is new (if your going through multiple backups for the same database you dont want to be restoring it multiple times)

Next we loop through the $SQLBackups array and look at the items one by one;

“$SQLBackups | foreach {
$temp = $_.name.split(“_”) # cuts up the db name into chunks (AppDB01_backup_2013_02_20 becomes “AppDB01”, “backup”, “2013”, “02”, “20”)
$compare = $temp[0] # make compare the first split item (i.e. AppDB01)
if ($compare -eq $filename -and $newdb -eq “yes”) {
$dbname = $dbname + $compare
$dbpath = $dbpath + $_.fullname
$newdb = “no”
} # if $compare = $filename (we have seen a backup for this db once already) and $newdb = “yes” add the dbname and path to our arrays
if ($compare -ne $filename) {
$newdb = “yes”
} # if the dbname has been seen before set $newdb to yes (which means the previous if statement will only work forevery second file seen (dangerous if there’s more than 3 backups found for the same database, I’ll update the script with a configurable counter for part 2
$filename = $compare # make filename the same as compare to keep the last used db name as we go around the loop
}

This loop will give us two populated arrays, one for dbname and one for dbpath where the values match ($dbname[0] is the name of the db for the backup at path $dbpath[0]). Results will look similar to below

 

in the next post I’ll discuss what to do with these arrays once we have them populated