PowerShell – SQL Database Refresh -Restore – Multiple Databases

.SYNOPSIS

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

.DESCRIPTION

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

\\SQLSERVER20\f$\PowerSQL\ABC_20160511_104239.BAK 

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

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

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

. Reference LINK
http://www.webofwood.com/2012/05/11/powershell-function-to-get-last-sql-server-backup-file/

#>

 

 

<#  
.SYNOPSIS  
 
The purpose of the script is to restore databse/s from a database backup. The requirement is to refresh 'n' databases. 
  
.DESCRIPTION  
 
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" 
 
   
.NOTES   
Author  : Prashanth Jayaram 
Requires: Make sure you have all the required admin access on the server 
  
.EXAMPLE  
Invoke_SQLDBRestore -SourceServer HQDBSP18 -DestServer HQSPDBSP02 -RestoreDbList "SafetyDB,rtc,rtcab1"  
 
. Reference LINK 
http://www.webofwood.com/2012/05/11/powershell-function-to-get-last-sql-server-backup-file/   
 
  
#> 
 
Function Invoke_SQLDBRestore 
{ 
 
param ([Parameter(Mandatory=$true,Position=0)][String]$SourceServer,  
[Parameter(Mandatory=$true,Position=1)][String]$DestServer, 
[Parameter(Mandatory=$true,Position=2)][String]$RestoreDbList) 
 
#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 
#SourceServer 
$Backupserver=$SourceServer 
$targetServer = $DestServer 
##Restore DB List 
#$RestoreDbList = "SafetyDB,rtc,rtcab1" 
 
 

Function BackupFile 
{ 
Param( 
    [Parameter(Mandatory=$true,Position=0)] 
    [string]$server, 
    [Parameter(Mandatory=$true,Position=1)] 
    [string]$database 
) 
Function New-SMOconnection { 
    Param ( 
        [Parameter(Mandatory=$true)] 
        [string]$server, 
        [int]$StatementTimeout=0 
    ) 
    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) 
    $smo 
} 
Function Get-LastBackupFile { 
    Param( 
        [string]$server, 
        [string]$database 
    ) 
    <#   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 
    $smo.ConnectionContext.Disconnect() 
    # Return the result 
    $rs.Tables[0].Rows[0].Item('backup') 
} 
# Load SMO Assemblies 
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") 
  
# 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.replace(':','$') 
#$backup 
 
$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; 
        $smoRestore.Devices.Add($backupDevice) 
   
        #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) 
            $smoRestore.RelocateFiles.Add($smoRestoreFile) 
        } 
        #restore database 
        $smoRestore.SqlRestore($server) 
   
     
} 
 
 
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"

 

Flow:-

To Backup refer my previous backup post

PowerShell – Backup Specific group of SQL Databases