PowerShell: Restoring a whole heap of SQL Databases

PowerShell
is one of those things that falls into my “other duties as assigned” repertoire. 
It’s something that I’ve used for years to get things done but it’s not often I encounter
a Dev at a customer that has worked with it much.  In my honest opinion, I think
adoption would increase if the PowerShell studio had intellisense or if it was just
another project type in Visual Studio.  That’s purely my opinion and is not a
reflection on my employer in any way. 

In any case, when I get pinged to help someone on a PowerShell task, I’ll usually
jump on it.  A customer at a large company came to me with a problem.  He
wanted to restore about 500 databases from their production environment to a development/staging
environment.  He
found this script that should theoretically get the job done but he was running into
a few problems.  The problems he was encountering were mostly related to
different drive mappings and different logical database names.  Of course, we
had to use a trick to get to that point.

The Problem

He originally was receiving the following error:

“Exception calling "SqlRestore" with "1" argument(s): "Restore
failed for Server '<Server2>'. "

At :line:97 char:20

+       $restore.SqlRestore <<<< ($instance)

The line the error above references is the line where the Restore action is called
to be executed and the character is the 'e' on $restore.SQLRestore'”

While it’s not a very informative message, we can get more information by running
the command:

 $error[0]|format-list –force

This provides a lot more information on what the cause of the error is.  
For example, when I force an error on the SqlRestore method, I might get the above
error message by default but once I execute the above command, I’ll see:

“Exception            
: System.Management.Automation.MethodInvocationException: Exception calling "SqlRestore"
with "1" argument(s): "Restore failed for Server 'GREGVAR1\SQLEXPRESS2'.
" ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore
failed for Server 'GREGVAR1\SQLEXPRESS2'.  ---> Microsoft. SqlServer.Management.Common.ConnectionFailureException:
Failed to connect to server GREGVAR1\SQLEXPRESS2. ---> System.Data.SqlClient.SqlException:
A network-related or instance-specific error occurred while establishing a connection
to SQL Server. The server was not found or was not accessible. Verify that the instance
name is correct and that SQL Server is configured to allow remote connections. (provider:
SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)…”

So, after he ran the magic command, the errors he was actually experiencing were the
following:

- The script expects the database’s logical name to be the file name of the database.

Some of the databases had a full text catalog associated with it.  The script  
was not handling the relocation of the full text catalog either.  

Many
thanks to Michiel Wories for initially introducing me to that really cool command
a year or so ago.

The Research

So, once we knew the problem, we needed a method to get additional information from
the backup file.  Enter the Restore.ReadFileList(…)
method.  You can use this method like the following:

  $server = New-Object("Microsoft.SqlServer.Management.Smo.Server") 


$instance

$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") 


($restorefile, "File") $smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore") #restore
settings $smoRestore.NoRecovery = $false; $smoRestore.ReplaceDatabase = $true; $smoRestore.Action = "Database" $smoRestore.PercentCompleteNotification = 10; $smoRestore.Devices.Add($backupDevice) #
Get Database Logical File Names   $sourceLogicalNameDT = $smoRestore.ReadFileList($server)  

The ReadFileList gives us a DataTable which
contains tons of additional information about the contents of the file we are attempting
to restore.  Once we  have that object, we clearly saw that it contains
all of the information we could possibly need:

LogicalName          : foodb

PhysicalName         : T:\…\DB\foo.mdf

Type                
: D

FileGroupName        : PRIMARY

Size                
: 104857600

MaxSize             
: 35184372080640

FileId              
: 1

CreateLSN            : 0

DropLSN             
: 0

UniqueId             :
4992c6f2-2282-4391-851e-d1177ab03920

ReadOnlyLSN          : 0

ReadWriteLSN         : 0

BackupSizeInBytes    : 30081024

SourceBlockSize      : 512

FileGroupId          : 1

LogGroupGUID         :

DifferentialBaseLSN  : 1297000000068200037

DifferentialBaseGUID : 69e8b951-7db1-4a1b-b0fc-e2cb012b3bcf

IsReadOnly           : False

IsPresent            : True

LogicalName          : foodb_log

PhysicalName         : R:\…\Logs\foo_log.LDF

Type                
: L

…

LogicalName          : sysft_OtherTables

PhysicalName         : T:\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\OtherTables004e

Type                
: F

…

Now that we have all of this great information, we need to iterate through the rows
and put the values we care about into some local variables.  For that, we can
use the foreach construct:

  $FileType = "" 


foreach($Row in $sourceLogicalNameDT) { #
Put the file type into a local variable. # This
will be the variable that we use to find out which file #
we are working with. $FileType = $Row["Type"].ToUpper() #
If Type = "D", then we are handling the Database File name. If ($FileType.Equals("D")) { $sourceDBLogicalName = $Row["LogicalName"] } #
If Type = "L", then we are handling the Log File name. elseif ($FileType.Equals("L")) { $sourceLogLogicalName = $Row["LogicalName"] } #
If Type = "F", then we are handling hte Full Text Catalog File Name. elseif ($FileType.Equals("F")) { $sourceFTSLogicalName = $Row["LogicalName"] #
We may also want to grab the full path of the Full Text catalog. $sourceFTSPhysicalName = $Row["PhysicalName"] } }

The Solution

Now, that we have all of the data we need, we can populate the Relocate
File objects so that the SqlRestore object will know how to handle these additional
files:

  #specify
new data and log files (mdf and ldf) $smoRestoreDBFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile") $smoRestoreLogFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile") #the
logical file names should be the logical filename stored in the backup media $smoRestoreDBFile.LogicalFileName = $sourceDBLogicalName $smoRestoreDBFile.PhysicalFileName = $mdfFilePath + "\" + $sourceDBLogicalName + ".mdf" $smoRestoreLogFile.LogicalFileName = $sourceLogLogicalName $smoRestoreLogFile.PhysicalFileName = $ldfFilePath + "\" + $sourceLogLogicalName + ".ldf" $smoRestore.RelocateFiles.Add($smoRestoreDBFile) $smoRestore.RelocateFiles.Add($smoRestoreLogFile) #
Check to see if the $SourceFTSLogicalName is empty or not. If its not empty #
then we do have a full text catalog present and thus we add the appropriate #
entries to restore those files. If the variable is empty we just continue with # the
restore. if (![String]::IsNullOrEmpty($sourceFTSLogicalName)) { "We
DO have a Full Text Catalog in our Backup" #
Adding full text catalog restore parameters. $smoRestoreFTSFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile") $smoRestoreFTSFile.LogicalFileName = $sourceFTSLogicalName #
Here we specify the new location by truncating the first 45 characters in the path #
that is specified on the backup file. This could be done a better way. $smoRestoreFTSFile.PhysicalFileName = $ftsFilePath + "\" + 


$sourceFTSPhysicalName.Substring(45) $smoRestore.RelocateFiles.Add($smoRestoreFTSFile) }

Lastly, we just need to execute the Restore command and clear out the variables for
the next iteration of the loop.

  #
Restore Database $smoRestore.SqlRestore($server)  #
We now clear the variables before the next loop starts Remove-Variable sourceDBLogicalName Remove-Variable sourceLogLogicalName Remove-Variable smoRestoreDBFile Remove-Variable smoRestoreLogFile #
If a full text catalog was present, we clear those variables too if (![String]::IsNullOrEmpty($sourceFTSLogicalName)) { Remove-Variable sourceFTSLogicalName Remove-Variable smoRestoreFTSFile }

And that’s all there is to it.  Keep in mind that these are the guts of the script
and not the full contents.  If there’s interest, I can post the whole script. 
Just drop a comment.

Enjoy!