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