PowerShell – SQL Database Refresh -Restore – Multiple Databases


The purpose of the script is to restore database/s from a database backup. The requirement is to refresh ‘n’ databases.


The advantage is that the flexibility of passing restoreDbList to restore specific group of databases.

The Restore databases has three mandatory paramters

param ([Parameter(Mandatory=$true,Position=0)][String]$SourceServer – Source where you have the backup files
[Parameter(Mandatory=$true,Position=1)][String]$DestServer – Target Server where the databases to be restored
[Parameter(Mandatory=$true,Position=2)][String]$RestoreDbLis – List of databases to be restored on DestServer and datbases are separated by comma – “rtc,SafetyDB”

After the backup, the physical file name details and converting the path to UNC is done with the help of Function BackupFile. It returns the UNC path of actual backup file located on the source server.

For example, The backup of ABC databases initiated on SQLSERVER20 and dump got created on F:\PowerSQL\ABC_20160511_104239.BAK then the function will return the following value


For the database restore have used the readily available code from the internet. The Function Invoke-DatabaseRestore uses the target server and actial backup physical location to restore the database

The successive restore will overwrite the target database

Author : Prashanth Jayaram
Requires: Make sure you have all the required admin access on the server

Invoke_SQLDBRestore -SourceServer HQDBSP18 -DestServer HQSPDBSP02 -RestoreDbList “SafetyDB,rtc,rtcab1”

. Reference LINK

Powershell Function to Get Last SQL Server Backup File




The purpose of the script is to restore databse/s from a database backup. The requirement is to refresh 'n' databases. 
The advantage is that the flexibility of passing dblist to restore specific group of databases. 
The Restore databases has three mandatory paramters 
param ([Parameter(Mandatory=$true,Position=0)][String]$SourceServer - Source where you have the backup files  
[Parameter(Mandatory=$true,Position=1)][String]$DestServer - Target Server where the databases to be restored 
[Parameter(Mandatory=$true,Position=2)][String]$RestoreDbLis - List of databases to be restored on DestServer and datbases are separated by comma - "rtc,SafetyDB" 
Author  : Prashanth Jayaram 
Requires: Make sure you have all the required admin access on the server 
Invoke_SQLDBRestore -SourceServer HQDBSP18 -DestServer HQSPDBSP02 -RestoreDbList "SafetyDB,rtc,rtcab1"  
. Reference LINK 
Function Invoke_SQLDBRestore 
param ([Parameter(Mandatory=$true,Position=0)][String]$SourceServer,  
#Load the required assemlies SMO and SmoExtended. 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null 
$targetServer = $DestServer 
##Restore DB List 
#$RestoreDbList = "SafetyDB,rtc,rtcab1" 

Function BackupFile 
Function New-SMOconnection { 
    Param ( 
    If(!(Test-Connection -ComputerName ($server.Split('\')[0]) -Quiet -Count 1)) { 
        Throw "Could not connect to SQL Server $server." 
    $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server) 
    $conn.applicationName = "PowerShell SMO" 
    $conn.StatementTimeout = $StatementTimeout 
    Try {$conn.Connect()} 
    Catch {Throw $Error} 
    if ($conn.IsOpen -eq $false) { 
        Throw "Could not connect to SQL Instance $server." 
    $smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn) 
Function Get-LastBackupFile { 
    <#   Use a hereto to construct the T-SQL 
        You will notice the query eliminates any snapshots. This is because we 
        sometimes have VEEAM backups on some servers. 
    $qry = @" 
DECLARE @dbname sysname  
SET @dbname = '$database' 
SELECT  f.physical_device_name as [backup] 
FROM    msdb.dbo.backupset AS s WITH (nolock) INNER JOIN 
            msdb.dbo.backupmediafamily AS f WITH (nolock) ON s.media_set_id = f.media_set_id 
WHERE   (s.database_name = @dbname) AND (s.type = 'D') AND (f.device_type <> 7)  
        AND (s.backup_finish_date = (SELECT MAX(backup_finish_date) 
FROM         msdb.dbo.backupset WITH (nolock) 
WHERE     (database_name = @dbname) AND (type = 'D') AND (is_snapshot = 0))) 
    # Get an SMO Connection 
    $smo = New-SMOconnection -server $server 
    # most appropriate to use MSDB 
    $db = $smo.Databases["msdb"] 
    # Execute query with results 
    $rs = $db.ExecuteWithResults($qry) 
    # SMO connection is no longer needed 
    # Return the result 
# Load SMO Assemblies 
# Call the function and trap any error 
Try {$backup = Get-LastBackupFile -server $server -database $database} 
Catch { 
    $ex = $Error[0].Exception 
    Write-Host $ex.Message 
    While($ex.InnerException) { 
        $ex = $ex.InnerException 
        Write-Host $ex.Message 
<# Verify the file   
    NOTE: most developent and run-time is performed on remote servers 
    so there may be a need to convert to UNC format 
$backup = Join-Path "\\$($server.split('\')[0])" $backup.replace(':','$') 
if(!(Test-Path $backup)) { 
    Throw "Database backup $backup not found" 
return  $backup 
function invoke-DatabaseRestore { 
    param ([String]$SQLServer="(local)", $BackupPath, [String]$BackupFileFilter = "") 
    #load assemblies 
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null 
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null 
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null 
        $backupFile = $BackupPath 
        #we will query the database name from the backup header later 
        $server = New-Object ( "Microsoft.SqlServer.Management.Smo.Server" ) $SQLServer 
        $backupDevice = New-Object"Microsoft.SqlServer.Management.Smo.BackupDeviceItem" ) ($BackupPath, "File") 
        $smoRestore = new-object( "Microsoft.SqlServer.Management.Smo.Restore" ) 
        $backupDevice| FL * 
        #Get default log and data file locations http://sqlblog.com/blogs/allen_white/archive/2009/02/19/finding-your-default-file-locations-in-smo.aspx 
        $DataPath = if ($server.Settings.DefaultFile.Length -gt 0 ) { $server.Settings.DefaultFile } else { $server.Information.MasterDBLogPath } 
        $LogPath = if ($server.Settings.DefaultLog.Length -gt 0 ) { $server.Settings.DefaultLog } else { $server.Information.MasterDBLogPath } 
        #restore settings 
        $smoRestore.NoRecovery = $false; 
        $smoRestore.ReplaceDatabase = $true; 
        $smoRestore.Action = "Database" 
        $smoRestore.PercentCompleteNotification = 10; 
        #get database name from backup file 
        $smoRestoreDetails = $smoRestore.ReadBackupHeader($server) 
        #display database name 
        "Database Name from Backup Header : " +$smoRestoreDetails.Rows[0]["DatabaseName"] 
        #give a new database name 
        $smoRestore.Database = $smoRestoreDetails.Rows[0]["DatabaseName"] 
        #Relocate each file in the restore to the default directory 
        $smoRestoreFiles = $smoRestore.ReadFileList($server) 
        foreach ($File in $smoRestoreFiles) { 
            #Create relocate file object so that we can restore the database to a different path 
            $smoRestoreFile = New-Object"Microsoft.SqlServer.Management.Smo.RelocateFile" ) 
            #the logical file names should be the logical filename stored in the backup media 
            $smoRestoreFile.LogicalFileName = $File.LogicalName 
            $smoRestoreFile.PhysicalFileName = $( if($File.Type -eq "L"{$LogPath} else {$DataPath} ) + "\" + [System.IO.Path]::GetFileName($File.PhysicalName) 
        #restore database 
foreach($db in $RestoreDbList.split(",")) 
$backupFile= BackupFile -server $Backupserver -database $db 
invoke-DatabaseRestore -SQLServer $targetSErver -BackupPath $backupFile 
Invoke_SQLDBRestore -SourceServer AQDBSP18 -DestServer AQSPDBSP02 -RestoreDbList "SafetyDB,rtc,rtcab1"



To Backup refer my previous backup post

PowerShell – Backup Specific group of SQL Databases





About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL and tagged , , . Bookmark the permalink.

7 Responses to PowerShell – SQL Database Refresh -Restore – Multiple Databases

  1. kyo says:

    Hello Prashant,

    You made my day by providing that Backup and Restore automation and the logins migration from powershell. hats off to your efforts towards helping us .


  2. kyo says:

    okay, thought there would be a away without restarting it.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s