Automate restore from Rubrik with PowerShell (part 2)

As promised during my last Rubrik blog post I’m writing a second one to explain how to:

• log your different steps in a file
• add an integrity check after the restore
• and also improve my first scripting

I will start by the end and improve my first scripts by creating functions for each step of the process.
With Rubrik you can have multiple appliances (servers), it means that each server/instance of you SQL Server environment will be dispatched on those servers and backup/restore operations have to take place in the good Rubrik server.
To connect to multiple Rubrik servers I created the Connect-dbiRubrik function:

Function Connect-dbiRubrik(){
	param(
		[Parameter(Mandatory=$true)]
        $RubrikServer,
		[PSCredential]
		$Credential
	)
    #Rubrik connection
	If (-not $Credential){
		$Credential = Get-Credential;
	}
		$Connection = Connect-Rubrik -Server $RubrikServer -Credential $Credential;

    return $Connection;
}

This function will help us to find our protected databases on all Rubrik appliances.
To get also the databases from Rubrik a new function has been created with, as mandatory parameter, an instance name and ,as optional parameters, a database name and a switch to add or not the system databases. This function will retrieve the instance databases part or not of an AlwaysOn Availability Group and fill a custom object to store some information about the database:

function Get-dbiRubrikDatabase(){
	param(
		[CmdletBinding()]
		[Parameter(Mandatory=$true)]
		[string[]]
        $SqlInstance,
		[String]
        $Database,
        [switch]
        $IncludeSystemDatabases
	)
	
	$DatabaseList = [System.Collections.ArrayList] @();
	
	@($SqlInstance) | Foreach-Object -Process {
		$ServerName = $_;
		## StandAlone databases
		$Command = 'Get-RubrikDatabase -ServerInstance $ServerName';

		If ($Database){
			$Command += ' -Database $Database';
		}

        If ($IncludeSystemDatabases) {
            $Command += ' | Where-Object isrelic -eq $false';
            }
        Else {
            $Command += ' | Where-Object {($_.isrelic -eq $false) -and ($_.name -notin ("master","msdb","model"))} '; 
            }
		
		Write-Debug -Message "Command to execute for StandAlone databases: $($Command)";
		$StandAloneDatabases = Invoke-Expression -Command $Command;
		
		@($StandAloneDatabases) | Foreach-Object -Process { `
			$PSDatabase = [pscustomobject] @{
				ServerName     	= "$($ServerName)"
				DatabaseName   	= "$($_.Name)"
				SLA 			= "$($_.effectiveSlaDomainName)"
				DatabaseID  	= "$($_.id)"
				ParentID  	    = "$($_.instanceId)"
				RubrikServer    = "$($rubrikConnection.Server)"
			}; `
			$Null = $DatabaseList.Add($PSDatabase); `
			$PSDatabase = $Null; `
		};
		$StandAloneDatabases 	= $Null;
		$Command 				= $Null;
		
		$AAGs = Get-dbaAvailabilityGroup -SqlInstance $ServerName -WarningAction SilentlyContinue ;


		If ($AAGs){
			@($AAgs) | Foreach-Object -Process {

                $Command = 'Get-RubrikDatabase -AvailabilityGroupName $_.AvailabilityGroup';
                
                If ($Database){
			        $Command += ' -Database $Database';
		        } 
                $Command += ' | Where-Object isrelic -eq $false -WarningAction SilentlyContinue';
                
				$AAGDatabases = Invoke-Expression -Command $Command;
                $Command = $null;

				@($AAGDatabases) | Foreach-Object -Process { `
					$PSDatabase = [pscustomobject] @{
						ServerName     	= "$($ServerName)"
						DatabaseName   	= "$($_.Name)"
						SLA 			= "$($_.effectiveSlaDomainName)"
						DatabaseID  	= "$($_.id)"
						ParentID  		= "$($_.availabilityGroupId)"
				        RubrikServer    = "$($rubrikConnection.Server)"
					}; `
					$Null = $DatabaseList.Add($PSDatabase); `
					$PSDatabase = $Null; `
				};
				$AAGDatabases 	= $Null;
			};
		};
		
		$ServerName = $Null;
	};
		
	return $DatabaseList;
}

Now, to log the required information during our restore test we need to create a log file and to populate it. This log file will contain the instance name with current date and time.
A function to create such a file has been also created to remove for example the possible in the instance name and to add the timestamp:

function Convert-dbiInstanceToFileName(){
	param(
		[Parameter(Mandatory=$true)]
        $SqlInstance,
		[Parameter(Mandatory=$true)]
        $Title
	)
	$SqlInstance_Str = $SqlInstance.Replace('','_');
	return "$($Title)_$($SqlInstance_Str)" + '_' + (((Get-Date) -f 'yyyyMMddHHmmss') -replace 'D', '') + '.txt';
}

Once the file name is generated we can create the file and write the desired information in it. The new function called Out-dbiLog will do that:

function Out-dbiLog() {
param(
[Parameter(Mandatory=$true)]
[String] $Message,
[Parameter(Mandatory=$true)]
[String] $LogPath
)

If (-not (Test-Path -Path $LogPath)) {
Try{
$Null = New-Item -Path $LogPath -ItemType 'file';

'['+(Get-Date -f 'yyyy-MM-dd HH:mm:ss') +'] ' + $Message | Out-File -FilePath $LogPath -Append;
}
catch {
Write-Host "Function 'Out-dbiLog': cannot create file located at $($LogPath)"  -ForegroundColor DarkRed -BackgroundColor Black;
}
}
Else {
'['+(Get-Date -f 'yyyy-MM-dd HH:mm:ss') +'] ' + $Message | Out-File -FilePath $LogPath -Append;
}
}

After the database is restored we can run an integrity check to be sure that the database is in a good shape. I will just use the dbatools cmdlet Invoke-dbaQuery with the switch -MessagesToOutput:

Invoke-dbaQuery -SQLInstance $DestinationInstance -QueryTimeout 14400 -Database $destDBName -Query "DBCC CHECKDB WITH NO_INFOMSGS" -MessagesToOutput;

To finalize we can plan a scenario where we want to restore a complete production instance to a test instance, database by database to valid our backup solution, once done run an integrity check and after drop the restored database and log all those actions. Hereby the script to do that:

$SourceInstance = 'ProdInst';
$DestinationInstance = 'TestInst';
[System.Collections.ArrayList] $statusList = @();

$credential = Get-Credential;
$RubrikServers = ('168.22.2.3','168.22.2.4');

#LOG
$LogFileName = Convert-dbiInstanceToFileName -SqlInstance $SourceInstance -Title 'Restore_Test';
$LogPath = 'c:dbiTestRestore';
Out-dbiLog -Message "Test Restore from source instance $SourceInstance to destination instance $DestinationInstance " -LogPath "$LogPath$LogFileName";

#Retrieve all databases on both Rubrik servers
$Dbs = @();
foreach ($RubrikServer in $RubrikServers) {
    $connect = Connect-dbiRubrik -RubrikServer $RubrikServer -Credential $credential;

    $Dbs += Get-dbiRubrikDatabase -SqlInstance $SourceInstance;
}

#Remove the database with unprotected SLA 
$Dbs = $Dbs | Where-Object SLA -ne 'UNPROTECTED';

#Restore the selected databases
foreach ($Db in $Dbs) {
    #LOG
    Out-dbiLog -Message "Start to restore database $($db.DatabaseName)" -LogPath "$LogPath$LogFileName";

    #Change database name on destination instance to visualize it quickly
    $destDBName = 'Rest_' + $Db.DatabaseName;

    #need to connect to the Rubrik server where the database is protected
    $connect = Connect-dbiRubrik -rubrikserver $Db.RubrikServer -Credential $credential;

    $Restore = Restore-dbiRubrikDatabase -Source $SourceInstance -Destination $DestinationInstance -DatabaseName $db.DatabaseName -NewDatabaseName $destDBName -TargetRestoreTime $date;

    #Loop until restore is finished
    $progress = 0;
    $RestoreID = $Restore.ID
    Do
    {
        #Search status of the restore
        $status = (Get-RubrikRequest -id $RestoreID -Type 'mssql'); 
        Write-Debug $status.status;

        #show a progress bar for the restore
        If ($status.status -eq 'RUNNING') {
            $progress = $status.progress; 
            }
        else {
            $progress = 100;
        }
        Write-Progress -Activity Restoring -Status $status.status -PercentComplete $progress -CurrentOperation "Restore database $destDBName"
        
        Start-Sleep -Seconds 1;
    } Until ($status.status -in ('SUCCEEDED','FAILED'));

    $null = $statusList.Add($status);

    If ($status.status -eq 'SUCCEEDED') {
        #LOG
        Out-dbiLog -Message "Database $($db.DatabaseName) restored successfully" -LogPath "$LogPath$LogFileName";

        #run a dbbc checkdb
        #QueryTimeout of 4h
        $DBCC = Invoke-dbaQuery -SQLInstance $DestinationInstance -QueryTimeout 14400 -Database $destDBName -Query "DBCC CHECKDB WITH NO_INFOMSGS" -MessagesToOutput;
        
        #LOG
        If ([string]::IsNullOrEmpty($DBCC)){
            Out-dbiLog -Message "Integrity Check for database $($db.DatabaseName) done successfully" -LogPath "$LogPath$LogFileName";
            }
        Else {
            Out-dbiLog -Message "Integrity Check for database $($db.DatabaseName) failed" -LogPath "$LogPath$LogFileName";
            }

        #drop database after
        $drop = Remove-DbaDatabase -SqlInstance $DestinationInstance -Database $destDBName -Confirm:$false;
    }

}

$statusList | FT status, starttime, endtime, error;

As you may have noticed one function is missing, Restore-dbiRubrikDatabase, I will point out this one in another blog post.
I hope those script could help.
Happy scripting.