Share via


SQL Fun(ctions)

To start with, a disclaimer: I’m not a SQL person.  No, not at all.  I just find myself supporting services that use MSSQL as a backend.  Here are a few functions I’ve recently written to help keep my sanity.

PSH-to-HTML formatting courtesy of https://out-web.blogspot.com/2007/11/powershell-syntax-highlighting-for-html.html

 

#region MSSQL functions

function Get-MsSqlInstance {
    < #
     .synopsis
     Returns the instance name for the specified server in form (computerName)\(instanceName).

     .description
     Returns the instance name for the specified server in form (computerName)\(instanceName).  If multiple instance names found, returns $null but lists instances on Warning filehandle.  If (computerName\(instanceName) is supplied, verifies instance exists on computer.

     .parameter dbServer
     Computer to query for SQL server and instances.  Defaults to localhost.  Optionally, if instance is supplied, verifies instance exists.

     .example
     Get-MsSqlInstance -dbServer sqlServer1

     sqlServer1\INSTANCE1

     This will return the single instance found on STDOUT.

     .example
     Get-MsSqlInstance -dbServer sqlServer2

     WARNING: More than one instance found:
      sqlServer2\INSTANCE1
      sqlServer2\INSTANCE2

     This will return nothing on STDOUT, but will display a warning listing the two instances.

     .example
     Get-MsSqlInstance -dbServer sqlServer2\INSTANCE1

     sqlServer2\INSTANCE1

     This will return the instance passed in on STDOUT, indicating it was found.

     .example
     Get-MsSqlInstance -dbServer sqlServer2\INSTANCE3

     WARNING: Instance sqlServer2\INSTANCE3 not found.

     This will return nothing on STDOUT, but will display a warning about the missing instance.

     .example
     Get-MsSqlInstance -dbServer nonSqlServer1

     WARNING: No SQL Server instances found on nonSqlServer1

     This will return nothing on STDOUT, but will display a warning about the missing instance.

     .example
     Get-MsSqlInstance -dbServer nonSqlServer1\INSTANCE1

     WARNING: No SQL Server instances found on nonSqlServer1

     This will return nothing on STDOUT, but will display a warning about the missing instance.

     .inputs
     None.

     .outputs
     [string]

     .link
     None.
     #>

     #region inner code
    < #
     (separate comment block so it doesn't show up in Get-Help output)
     Changelog

     2013-02-15  timdunn     completed help, ready for release
     #>

     param (
         [string]$dbServer = $env:COMPUTERNAME.ToUpper()
     );

     if ($dbServer -match '\\') {
         $instanceName = $dbServer -replace ".*\\";
         $dbServer = $dbServer -replace "\\.*";
     }

     [String[]]$instances = (Get-Service -ComputerName $dbServer -DisplayName 'SQL Server (*' -ErrorAction SilentlyContinue | Select-Object -Property DisplayName) -replace '.*\(' -replace '\).*';
     if ($instances.Count -eq 0) {
         Write-Warning "No SQL Server instances found on $dbServer";
     } elseif ($instanceName) {
         $output = $null;
         $instances | % { if ($_ -eq $instanceName) { $output = "$dbServer\$_"; } }
         if ($output) { $output; } else { Write-Warning "Instance $dbServer\$instanceName not found."; }
     } elseif ($instances.Count -eq 1) {
         "$dbServer\$($instances[0])";
     } else {
         $delim = "`n`t$dbServer\";
         Write-Warning ("More than one instance found:$delim{0}" -f [String]::Join($delim, $instances));
     }
     #endregion
}

function Get-MsSqlDbName {
    < #
     .synopsis
     Returns list of databases on specified computer.

     .description
     Returns the databases found on the specified computer.  If multiple SQL instances are running on the specified computer, it must be specified as (computerName\(instanceName).

     .parameter dbServer
     Computer to query dabases.  Defaults to localhost.  If multiple SQL instances are running on the specified computer, it must be specified as (computerName\(instanceName).

     .parameter dbName
     Database name to verify exits.  Default behaviour is to display all databases.  Note this is an exact match (-eq), not a RegEx match (-match).

     .example
     Get-MsSqlDbName sqlServer1

     Server               Name
     ------               ----
     sqlServer1\INSTANCE1 master
     sqlServer1\INSTANCE1 model
     sqlServer1\INSTANCE1 msdb
     ...

     This will return the databases on the specified host.

     .example
     Get-MsSqlDbName sqlServer2

     WARNING: More than one instance found:
      sqlServer2\INSTANCE1
      sqlServer2\INSTANCE2

     This will return nothing on STDOUT, but will display a warning listing the two instances.

     .example
     Get-MsSqlDbName sqlServer2\INSTANCE1

     Server               Name
     ------               ----
     sqlServer2\INSTANCE1 master
     sqlServer2\INSTANCE1 model
     sqlServer2\INSTANCE1 msdb

     This will return the instance passed in on STDOUT, indicating it was found.

     .example
     Get-MsSqlDbName sqlServer2\INSTANCE3

     WARNING: Instance sqlServer2\INSTANCE3 not found.

     This will return nothing on STDOUT, but will display a warning about the missing instance.

     .example
     Get-MsSqlDbName nonSqlServer1

     WARNING: No SQL Server instances found on nonSqlServer1

     This will return nothing on STDOUT, but will display a warning about the missing instance.

     .inputs
     None.

     .outputs
     [PSAutomationObject] with properties Server and Name

     .link
     None.
     #>

     #region inner code
    < #
     (separate comment block so it doesn't show up in Get-Help output)
     Changelog

     2013-02-15  timdunn     completed help, ready for release
     #>

     param (
         [string]$dbServer = $env:COMPUTERNAME,
         [string]$dbName = $null
     );

     try {
         $dbServer =  Get-MsSqlInstance $dbServer;
         if (!$dbServer) { return; }

         #load assemblies
         @(
             "Microsoft.SqlServer.SMO",
             "Microsoft.SqlServer.SmoExtended",
             "Microsoft.SqlServer.ConnectionInfo",
             "Microsoft.SqlServer.SmoEnum"
         ) | % {
             $assembly = $_;
             Write-Progress (Get-Date) "Loading $assembly";
             [System.Reflection.Assembly]::LoadWithPartialName($assembly);
         } | Out-Null;

         #connect to instance of MSSQL server
         Write-Progress (Get-Date) "Creating Server Management Object ($dbServer)";
         $serverSMO = New-Object Microsoft.SqlServer.Management.SMO.Server($dbServer);
         if (!$serverSMO) { Write-Warning "Cannot connect to -dbServer $dbServer"; return; }

         $output = $serverSMO.Databases | Select-Object -Property @{
             n = 'Server'; e = { $dbServer; }
         }, Name | Sort-Object -Property Name;
         if ($dbName) {
             $output | Where-Object { $_.name -eq $dbName; }
         } else {
             $output;
         }
     } catch {
         $global:__error = $_;
         Write-Warning "Unspecified error occured.  Check `$__error for details.";
         Write-Error -ErrorAction SilentlyContinue -Message "oops";
         return;
     }
     #endregion
}

function Test-DbIsPrimary {
    < #
     .synopsis
     Determines if specified database is primary.

     .description
     Returns tristate for if specified database is primary.
      1 for db is found and is primary.
      0 for db is found and is not primary.
     -1 for db is not found.

     .parameter dbServer
     Computer to query for database existence and status.  Defaults to localhost.  If multiple instances exist on the computer, it must be specified in the form of (computerName)\(instanceName).

     .paramenter dbName
     Database to query for existence and status.  Required.

     .example
     Test-DbIsPrimary -dbServer sqlServer1 -dbName database1

     WARNING: -dbName not specified, required.

     This will return nothing on STDOUT, but will display a warning about the missing database.

     .example
     Test-DbIsPrimary -dbServer nonSqlServer -dbName database3

     WARNING: No SQL Server instances found on nonSqlServer

     This will return nothing on STDOUT, but will display a warning about the missing instance.

     .example
     Test-DbIsPrimary -dbServer SqlServer2 -dbName database2

     WARNING: More than one instance found:
      sqlServer2\INSTANCE1
      sqlServer2\INSTANCE2

     This will return nothing on STDOUT, but will display a warning about the multiple instances.

     .example
     Test-DbIsPrimary -dbServer SqlServer1 -dbName primaryDb

     1

     .example
     Test-DbIsPrimary -dbServer SqlServer1 -dbName mirrorDb

     0

     .example
     Test-DbIsPrimary -dbServer SqlServer1 -dbName missingDb

     -1

     .inputs
     None.

     .outouts
     [string]

     .link
     None.
     #>

     #region inner code
    < #
     (separate comment block so it doesn't show up in Get-Help output)
     Changelog

     2013-02-15  timdunn     completed help, ready for release
     #>

     param (
         [string]$dbServer = $env:COMPUTERNAME,
         [string]$dbName = $null
     );
     try {
         if (!$dbName) { Write-Warning "-dbName not specified, required."; return; }
         $dbServer =  Get-MsSqlInstance $dbServer;
         if (!$dbServer) { return; }

         #load assemblies
         @(
             "Microsoft.SqlServer.SMO",
             "Microsoft.SqlServer.SmoExtended",
             "Microsoft.SqlServer.ConnectionInfo",
             "Microsoft.SqlServer.SmoEnum"
         ) | % {
             $assembly = $_;
             Write-Progress (Get-Date) "Loading $assembly";
             [System.Reflection.Assembly]::LoadWithPartialName($assembly);
         } | Out-Null;

         #connect to instance of MSSQL server
         Write-Progress (Get-Date) "Creating Server Management Object ($dbServer)";
         $serverSMO = New-Object Microsoft.SqlServer.Management.SMO.Server($dbServer);
         if (!$serverSMO) { Write-Warning "Cannot connect to -dbServer $dbServer"; return; }

         $db = $serverSMO.Databases | ? { $_.Name -eq $dbName };
         if ($db) {
             [int]($db.Status -eq 'Normal');
         } else {
             Write-Warning "-dbServer $dbServer does not have -dbName $dbName";
             -1;
         }
     } catch {
         $global:__error = $_;
         Write-Warning "Unspecified error occured.  Check `$__error for details.";
         Write-Error -ErrorAction SilentlyContinue -Message "oops";
         return;
     }
     #endregion
}

function BackUp-MsSqlDb {
    < #
     .synopsis
     Backup up the specified database on the specified computer and return a UNC path to the backup file.

     .description
     Backup up the specified database on the specified computer and return a UNC path to the backup file.  If multiple SQL instances are running on the specified computer, it must be specified as (computerName\(instanceName).

     .parameter dbName
     Name of database to back up.  Required.

     .parameter path
     Path of backup file.  Defaults to the SQL server's configured backup drive:\path\(computerName)-(databaseName)_(datetimestamp).bak.

     .parameter dbServer
     Computer to query dabases.  Defaults to localhost.  If multiple SQL instances are running on the specified computer, it must be specified as (computerName\(instanceName).

     .parameter force
     Overwrite file specified by -path if it exists.  Default is to halt.  Regardless if parameter is specified, a warning will be sent to the Warning filehandle.

     .parameter noCompression
     Do not compress the backup file.  Compression results in a substantially smaller file, but takes much longer.  Use this if the database will be restored locally.

     .example
     BackUp-MsSqlDb -dbServer sqlServer1 -dbName database1

     \\sqlServer1\c$\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Backup\sqlServer1-DATABASE1_2013-02-16_10-30-00.bak

     .example
     BackUp-MsSqlDb -dbServer sqlServer1

     WARNING: -dbName not specified, required.

     This will return nothing on STDOUT, but will display a warning about the missing database.

     .example
     BackUp-MsSqlDb -dbServer sqlServer2 -dbName database2

     WARNING: More than one instance found:
      sqlServer2\INSTANCE1
      sqlServer2\INSTANCE2

     This will return nothing on STDOUT, but will display a warning listing the two instances.

     .example
     BackUp-MsSqlDb -dbServer sqlServer2\INSTANCE1 -dbName database2

     \\sqlServer2\c$\Program Files\Microsoft SQL Server\MSSQL11.INSTANCE1\MSSQL\Backup\sqlServer2-DATABASE2_2013-02-16_11-00-00.bak

     .example
     BackUp-MsSqlDb -dbServer sqlServer2\INSTANCE3 -dbName database2

     WARNING: Instance sqlServer2\INSTANCE3 not found.

     This will return nothing on STDOUT, but will display a warning about the missing instance.

     .example
     Get-MsSqlDbName -dbServer nonSqlServer1 -dbName database3

     WARNING: No SQL Server instances found on nonSqlServer1

     This will return nothing on STDOUT, but will display a warning about the missing instance.

     .inputs
     None.

     .outputs
     [string]

     .link
     https://www.sqlmusings.com/2009/05/31/how-to-backup-sql-server-databases-using-smo-and-powershell

     .notes
     Upon successful completion, BackUp-MsSqlDb creates [hashtable]$global:__metaData with the following name/value:

         'dbBackupPath' = $path;
         'sourceDbServer' = $dbServer;
         'sourceDbName' = $dbName;

     #>

     #region inner code
    < #
     (separate comment block so it doesn't show up in Get-Help output)
     Changelog

     2013-02-15  timdunn     completed help, ready for release
     #>

     param (
         [string]$dbName = $null,
         [string]$dbServer = $env:COMPUTERNAME,
         [string]$path = $null,
         [switch]$force,
         [switch]$noCompression
     );

     try {
         if (!$dbName) { Write-Warning "-dbName not specified, required."; return; }
         $dbServer =  Get-MsSqlInstance $dbServer;
         if (!$dbServer) { return; }
         if ((Test-DbIsPrimary -dbServer $dbServer -dbName $dbName) -ne 1) {
             Write-Warning "-dbServer $dbServer -dbName $dbName is not primary";
             return;
         }

         #load assemblies
         @(
             "Microsoft.SqlServer.SMO",
             "Microsoft.SqlServer.SmoExtended",
             "Microsoft.SqlServer.ConnectionInfo",
             "Microsoft.SqlServer.SmoEnum"
         ) | % {
             $assembly = $_;
             Write-Progress (Get-Date) "Loading $assembly";
             [System.Reflection.Assembly]::LoadWithPartialName($assembly);
          } | Out-Null;

         $dateStamp = Get-Date -Format 'yyyy-MM-dd_hh-mm-ss';

         #connect to instance of MSSQL server
         $serverSMO = New-Object Microsoft.SqlServer.Management.SMO.Server($dbServer);
         if (!$serverSMO) { Write-Warning "Cannot connect to -dbServer $dbServer"; return; }
         $serverSMO.ConnectionContext.StatementTimeout = 0;

         #look for databases
         $dbObject = $serverSMO.Databases[$dbName];
         if (!$dbObject) {
             Write-Warning "-dbName $dbName not found on -dbServer $dbServer";
             return;
         }

         #what does the DB think its names is (correctly cased)
         $dbObjectName = $dbObject.Name;
         $backupName = "$dbObjectName backup";

         $dbServer = $dbServer -replace "^\s*" -replace "\s*$";
         $dbHost = $dbServer -replace '\\.*';

         #set backup path to server's backup directory if not specified.
         if (!$path) {
             $backupDirectory = $serverSMO.Settings.BackupDirectory;
             if ($env:ComputerName -ne '$dbHost') {
                 $backupDirectory = $backupDirectory -replace '^', "\\$dbHost\" -replace '\:', '$';
             }
             #Write-Host $dbHost
             #Write-Host $backupDirectory;
             $dbSource = $dbHost -replace '[\\\/\:\*\?\"\<\>\|]', '-';
             $path = Join-Path -Path $backupDirectory -ChildPath "${dbHost}-${dbObjectName}_$dateStamp.bak";
         }

         #-force or not, show warning if destination file exists. terminate unless -force specified
         if (Test-Path $path) {
             Write-Warning "-path $path exists";
             if (!$force) { return; }
         }

         #programmatic access to MSSQL server's backup operations
         $smoBackup = New-Object Microsoft.SqlServer.Management.SMO.Backup;
         if (!$smoBackup) {
             Write-Warning "Cannot create Microsoft.SqlServer.Management.SMO.Backup object";
             return;
         }

         #configure the backup
         $smoBackup.Action = "Database";
         $smoBackup.BackupSetDescription = "$backupName $datestamp";
         $smoBackup.BackupSetName = $backupName;
         $smoBackup.Database = $dbObjectName;
         $smoBackup.MediaDescription = "Disk";
         $smoBackup.Devices.AddDevice($path, "File");
         if (!$noCompression) { $smoBackup.CompressionOption = 'on'; }

         #do the needful
         Write-Progress "($(Get-Date)) Backing up $dbObjectName to" $path;
         $smoBackup.SqlBackup($serverSMO);

         #verify backup file exits, output network path
         if (Test-Path -Path $path) {
             $path;
             $global:__metaData = @{
                 'dbBackupPath' = $path;
                 'sourceDbServer' = $dbServer;
                 'sourceDbName' = $dbName;
             }
         } else {
             Write-Warning "Unable to backup -dbName $dbName to '$path'";
             return;
         }
     }
     catch {
         $global:__error = $_;
         Write-Warning "Unspecified error occured.  Check `$__error for details.";
         Write-Error -ErrorAction SilentlyContinue -Message "oops";
         return;
     }
     #endregion
}

function Restore-MsSqlDb {
    < #
     .synopsis
     Restores the specified database on the specified computer from a UNC path to the backup file.

     .description
     Restores the specified database on the specified computer from a UNC path to the backup file.  If multiple SQL instances are running on the specified computer, it must be specified as (computerName\(instanceName).  If the file is not on the specified computer, it will be copied over via robocopy.exe onto the specified computer's configured SQL backup folder.

     .parameter path
     Path of backup file.  Defaults to $global:__metaData.dbBackupPath if it was populated by Backup-MsSqlDb.  If value is not found, Restore-MsSqlDb

     .parameter dbName
     Name of database to restore to.  Defaults to the name of the file.  If the name already exists, Restore-MsSqlDb will emit a warning and halt.

     .parameter dbServer
     Computer to restore back onto.  Defaults to localhost.  If multiple SQL instances are running on the specified computer, it must be specified as (computerName\(instanceName).

     .example
     Restore-MsSqlDb -dbServer sqlServer1\INSTANCE1 -path \\sqlServer1\c$\Program Files\Microsoft SQL Server\MSSQL11.INSTANCE1\MSSQL\Backup\sqlServer1-DATABASE1_2013-02-16_10-30-00.bak

     DbServer             DbName
     --------             ------
     sqlServer1\INSTANCE1 sqlServer1-DATABASE1_2013-02-16_10-30-00.bak

     Restores a database from a local file.

     .example
     Restore-MsSqlDb -dbServer sqlServer2\INSTANCE2 -path \\sqlServer1\c$\Program Files\Microsoft SQL Server\MSSQL11.INSTANCE1\MSSQL\Backup\sqlServer1-DATABASE1_2013-02-16_10-30-00.bak

     DbServer             DbName
     --------             ------
     sqlServer2\INSTANCE2 sqlServer1-DATABASE1_2013-02-16_10-30-00.bak

     Restores a database from a remote file on sqlServer1, which will be first be copied onto sqlServer2's configured SQL backup folder.

     .example
     Restore-MsSqlDb sqlServer2

     WARNING: More than one instance found:
      sqlServer2\INSTANCE1
      sqlServer2\INSTANCE2

     This will return nothing on STDOUT, but will display a warning listing the two instances.

     .example
     Restore-MsSqlDb sqlServer2\INSTANCE3 database2 -path doesNotMatter.bak

     WARNING: Instance sqlServer2\INSTANCE3 not found.

     This will return nothing on STDOUT, but will display a warning about the missing instance.

     .example
     Restore-MsSqlDb nonSqlServer1 database3 -path doesNotMatter.bak

     WARNING: No SQL Server instances found on nonSqlServer1

     This will return nothing on STDOUT, but will display a warning about the missing instance.

     .inputs
     None.

     .outputs
     [PSAutomationObject] with following name/value pairs:
     DbServer : sqlServer1\INSTANCE1
     DbName   : sqlServer2-DATABASE1_2013-02-16_10-30-00.bak

     .link
     https://www.sqlmusings.com/2009/06/01/how-to-restore-sql-server-databases-using-smo-and-powershell

     .link
     https://www.codeproject.com/Articles/110908/SQL-DB-Restore-using-PowerShell

     .notes
     Upon successful completion Restore-MsSqlDb populates [hashtable]$global:__metaData with the following name/value pairs:

         'restoreDbServer' = $dbServer;
         'restoreDbName' = $dbName;

     If the file needed to be copied, [string[]]$global:__RoboCopyLog captures the output of the robocopy.exe command

     #>

     #region inner code
    < #
     (separate comment block so it doesn't show up in Get-Help output)
     Changelog

     2013-02-15  timdunn     completed help, ready for release
     #>

     param (
         [string]$path = $null,
         [string]$dbServer = $null,
         [string]$dbName = $null
     );

     try {
         if (!$path) { $path = $global:__metaData.dbBackupPath; }
         if (!$path) { Write-Warning "-path not specified, required."; return; }

         if ($dbServer -notmatch "\\") { $dbServer =  Get-MsSqlInstance $dbServer; }
         if (!$dbServer) { Write-Warning "-dbServer not specified nor discoverable, required."; return; }
         #load assemblies
         @(
             "Microsoft.SqlServer.SMO",
             "Microsoft.SqlServer.SmoExtended",
             "Microsoft.SqlServer.ConnectionInfo",
             "Microsoft.SqlServer.SmoEnum"
         ) | % {
             $assembly = $_;
             Write-Progress (Get-Date) "Loading $assembly";
             [System.Reflection.Assembly]::LoadWithPartialName($assembly);
         } | Out-Null;

         #connect to instance of MSSQL server
         Write-Progress (Get-Date) "Creating Server Management Object";
         $serverSMO = New-Object Microsoft.SqlServer.Management.SMO.Server($dbServer);
         if (!$serverSMO) { Write-Warning "Cannot connect to -dbServer $dbServer"; return; }
         $serverSMO.ConnectionContext.StatementTimeout = 0;

         #Can only restore on local drive of server.
         $dbHost = $dbServer -replace "\\.*";
         if (($path -match "^\\") -and ($path -notmatch "^\\\\$dbHost")) {
             if (Get-Command robocopy) {
             $backupDirectory = "\\$dbhost\$($serverSMO.Settings.BackupDirectory)" -replace ":", "$";
             Write-Progress (Get-Date) "Copying $path to $backupDirectory";
             $global:__RoboCopyLog = RoboCopy.exe (Split-Path -Parent $path) $backupDirectory (Split-Path -Leaf $path) /NP;
             $path = "$($serverSMO.Settings.BackupDirectory)\$(Split-Path -Leaf $path)";
             } else {
                 Write-Warning "Unable to find robocopy to copy $path to $backupDirectory";
                 return;
             }
         }

         #programmatic access to MSSQL server's backup devices
         $smoBackup = New-Object Microsoft.SqlServer.Management.SMO.BackupDeviceItem($path, "File");
         if (!$smoBackup) {
             Write-Warning "Cannot read -path $path as Microsoft.SqlServer.Management.SMO.Backup object";
             return;
         }

         #programmatic access to MSSQL server's restore operations
         $smoRestore = New-Object Microsoft.SqlServer.Management.SMO.Restore;
         if (!$smoRestore) {
             Write-Warning "Cannot create Microsoft.SqlServer.Management.SMO.Restore object";
             return;
         }

         #configure the restore
         $smoRestore.NoRecovery = $false;
         $smoRestore.ReplaceDatabase = $true;
         $smoRestore.Action = "Database";
         $smoRestore.PercentCompleteNotification = 10;
         $smoRestore.Devices.Add($smoBackup);

         #what does the backup file think the name is?

         $smoRestoreDetails = $smoRestore.ReadBackupHeader($serverSMO);
         $smoRestoreName = $smoRestoreDetails.Rows[0]["DatabaseName"];

         if ($dbName) {
             #we're restoring it to a different name
             $smoRestore.Database = $dbName;
         } else {
             $smoRestore.Database = Split-Path -Leaf $smoBackup.Name;
             $dbName = $smoRestore.Database;
         }

         $dbFileList = $smoRestore.ReadFileList($serverSMO);

         #restore MDF to new file based on DB name;
         $smoRestoreFile = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile;
         if (!$smoRestoreFile) {
             Write-Warning "Cannot create MDF Microsoft.SqlServer.Management.Smo.RelocateFile";
             return;
         }
         $masterDBPath = $serverSMO.Information.MasterDBPath;
         $smoRestoreFile.LogicalFileName = $dbFileList.Select("Type='D'")[0].LogicalName;
         $smoRestoreFile.PhysicalFileName = "$masterDBPath\${dbName}_Data.mdf";

         #restore Log to new file based on DB name;
         $smoRestoreLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile;
         if (!$smoRestoreFile) {
             Write-Warning "Cannot create Log Microsoft.SqlServer.Management.Smo.RelocateFile";
             return;
         }
         $masterDBLogPath = $serverSMO.Information.MasterDBLogPath;
         $smoRestoreLog.LogicalFileName = $dbFileList.Select("Type='L'")[0].LogicalName;
         $smoRestoreLog.PhysicalFileName = "$masterDBLogPath\${dbName}_Log.ldf";

         #change the MDF/LDF files to have the new name
         $smoRestore.RelocateFiles.Add($smoRestoreFile) | Out-Null;
         $smoRestore.RelocateFiles.Add($smoRestoreLog) | Out-Null;

         #-force or not, show warning if db exists. terminate unless -force specified
         if ($serverSMO.Databases[$smoRestore.Database]) {
             Write-Warning "-dbName $($smoRestore.Database) exists on -dbServer $dbServer";
             return;
         }

         #do the needful
         Write-Progress $(Get-Date) "Restoring $($smoRestore.Database)";
         $smoRestore.SqlRestore($serverSMO);

         #verify output
         if ($serverSMO.Databases[$smoRestore.Database]) {
             $serverSMO.Databases[$smoRestore.Database] | Select-Object @{
                 n = 'DbServer';
                 e = { $dbServer; }
             }, @{
                 n = 'DbName';
                 e = { $_.Name; }
             };
             if (($global:__metaData) -and ($global:__metaData.GetType().Name -eq 'Hashtable')) {
                 $global:__metaData.restoreDbServer = $dbServer;
                 $global:__metaData.restoreDbName = $dbName;
             }
         } else {
             Write-Warning "Unable to restore -dbName $($smoRestore.Database) to '$dbServer'";
             return;
         }

     }
     catch {
         $global:__error = $_;
         Write-Warning "Unspecified error occured.  Check `$__error for details.";
         Write-Error -ErrorAction SilentlyContinue -Message "oops";
         return;
     }
     #endregion
}

#endregion