Automatically restore a list of databases

I recently had a situation at my client where we were troubleshooting a SQL server hanging and one candidate was a faulty RAID array or SCSI controller. One option to explore was to restore all the databases to another server and run an Index rebuild and Update statistics jobs against these restored databases. This was because these jobs caused the server to hang. So we had 20 odd databases to restore. The same requirement, that of restoring a list of databases to another server was also required by another DBA team at another client, so my quest for automation would be used by my team members at another client.

The databases in both scenario's are backed up by a maintenance plan. As such there is a backup folder with a subfolder for each database, with the sub folder name being the name of the database being restored. This is quite a common scenario. So I could copy the whole backup folder and then iterate through the sub folders and restore each database contained in each backup file. 2 minor challenges, the restore location may be, and in both cases, is different, the 2nd challenge is that each database may have more than one mdf and ldf. My script caters for both of these scenarios.

The strategy is to write a function (encapsulated single unit of work) that restores one database from one backup file. It takes the backupfile name and the database name as input parameters. I've hard coded the restore location in the function, but this could be passed in as a 3rd parameter.

Once I have this function working, I can iterate through the folder structure and for each sub directory, grab the folder name, which would be the database name, and restore the database in the backup file in that sub folder.

Herewith the code:

FUNCTION Restore-Db ($BackupFile, $DbName)
{
$RestP = "K:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA"
$moveStr = ""
$q = "RESTORE FILELISTONLY FROM DISK = "+ $BackupFile
$b = Invoke-Sqlcmd -Query $q
$ln = $b | select logicalName, PhysicalName
foreach ($lnn in $ln) {$e = $lnn.PhysicalName.LastIndexOf("\")+1;$l =$lnn.PhysicalName.length; $ll = $l-$e; $phys = $lnn.PhysicalName.Substring($e,$ll); $moveStr = $moveStr+"MOVE '"+ $lnn.LogicalName + "' TO '"+$RestP+"\"+$phys+"', "}
$RestStr = "Restore Database "+$DbName+" FROM DISK = "+$BackupFile+" WITH "+$moveStr+ " STATS = 5"
Invoke-Sqlcmd -Query $RestStr -querytimeout 3600
}

$BackupsFiles = "F:\"
$dirs = dir $BackupsFiles  | Where {$_.psIsContainer -eq $true}
$dbs = $dirs | select name, fullname

foreach ($dbx in $dbs) {$BackupFile = gci -path $dbx.fullname -include *.bak -recurse; $DbName = $dbx.name; write-host "Restoring database $DbName from file $BackupFile";$BackupFile = "'"+$BackupFile+"'"; Restore-Db $BackupFile $DbName}

One point to take note of, the code does not cater for cases where there is more than one backup file per sub folder. If this is the case then you'll have to edit the function accordingly. Another point is I've put in a -querytimeout value of 3600, or 1 hour. If a very big database restore takes longer than this, adjust this value accordingly.

Its a beautiful thing when you can fire off a script and watch a list of 30 databases being restored, automatically, while you do other stuff. :-)