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. :-)

Check that all databases on all servers have been backed up

At the client that I work at, there are over 1500 databases on more than 200 SQL servers, ranging from SQL 2000 to 2008. For me to have a good nights sleep, I want to know that each and every database is being and has been backed up. I could check each databases properties, I could go through 200 emails, and other such tedious methods?! Heck no, read the title of this Blog!!!

Prior to SQL 2008 and the Powershell provider it ships with (sqlps.exe) you could either write your own cmdlets or using SQLPSX from Codeplex, thanks Chad. The beauty with Powershell and SMO and .Net is that a database is an object and the lastbackupdate is a property of that object. Grab the databases collection of a SQL server and for each database get the last backup date. Using T-SQL you'd join a few system tables and write some code. Thats for 1 server, for multiple servers you could use any one of a number of methods to get this information. All this is quite a bit more tedious than the PS approach.

The high level PS approach is I have a list of SQL servers, for each server go to the databases collection and return the database name and the last backup date. That's as simple as it gets and the code is not much more complicated. Well sort of.

To automate the above I can use the SQL Agent on my DBA Management server that has SQL 2008 client tools installed. I can create a SQL Agent Job that runs a Powershell step. How cool is that?!! So in the command window I type vanilla PS code. Whoop!! Thing is, and this is what to me a while to figure out, the PS shell that SQL agent runs is the vanilla shell, not the sqlps shell with the SQL snapins. So to get around this I created a profile file that applies to the native powershell shell that is used by all users.

I create the file and add the two entries using powershell:

ni C:\Windows\system32\WindowsPowerShell\v1.0\Microsoft.PowerShell_profile.ps1 -type "file"
ac -path C:\Windows\system32\WindowsPowerShell\v1.0\Microsoft.PowerShell_profile.ps1 -value "Add-PSSnapin SqlServerCmdletSnapin100"
ac -path C:\Windows\system32\WindowsPowerShell\v1.0\Microsoft.PowerShell_profile.ps1 -value "Add-PSSnapin SqlServerProviderSnapin100"

The great thing now is that, besides from SQL Agent, I can browse SQL like a directory. What I do from the SQL Agent Job is just that. The code:

$svl = 'SQLsvr1', 'SQL-Dev01', 'SQL2k-03'
foreach ($s in $svl) {gci sqlserver:sql\$s\default\databases | select parent, name, lastbackupdate| where {$_.lastbackupdate -lt $(Get-Date).AddDays(-7)}}

Thats it! The above queries my server list ($svl) and gets the properties (gci or get-childitme) of each database in the databases collection for each server. The specific property is the LastBackupDate and where its older than 7 days ago.

Work smart, sleep easy. ;-)