Infrastructure at your Service

One of our customer uses Rubrik as a solution to recover, manage, and secure all their data. In this context, Rubrik is used to backup and restore all the SQL Server instances.
We need to run restore tests in this validated environment every year. To do so we decide to create a PowerShell script to automate the restore of all user databases from a source instance (a production one) to a destination instance (a test one). Once the restore is done an integrity check has also to be performed to validate the restored databases.
To perform those actions we will use a famous PowerShell module which is the dbatools and also the Rubrik module.

The first step is to define the source and destination instances, and to retrieve all the user databases from the source instance via the dbatools cmdlet Get-DbaDatabase. If you want to select just one database or databases starting by a specific string you have to use the Where-Object (commented here):

# source and destination instance
$sourceInstance = 'Thor1'; 
$destInstance = 'Thor2';  

# retrieve all user databases for the source instance
$databases = Get-DbaDatabase -SqlInstance $instance -ExcludeSystem; #| Where-Object {$_.name -match '^Test'};

Once done, we need to connect to our Rubrik server, we will use its IP Address. The cmdlet Connect_Rubrik will prompt you for a user and a password for the Rubrik connection:

#IP Address of the Rubrk server
$RubrikServer = '172.168.2.2';

# Rubrik connection
$Connection = Connect-Rubrik -Server $RubrikServer;

Now that we are connected to Rubrik we will need to find the Rubrik instance ID of the destination instance. We will need this information later during the restore operation;

# Find instance ID of the destination instance
$DestInstID = Get-RubrikDatabase -ServerInstance $destInstance -Database master | select instanceId

To restore the database to the new instance we need to know the default Data and Log paths for this instance. We will get those information now:

# Find default path for the instance
$InstPath = Get-DbaDefaultPath -SqlInstance $destInstance;

We have now our source and destination instances, our list of source databases to restore and the Rubrik connection so we can loop on source databases and restore them to the destination instance.
This will be done by a Foreach which don’t ask too much effort.
At this stage we need to take care if the source database is part of an AlwaysOn Availability Group or not because the way to retrieve it in Rubrik will differ.
We will check for that the property AvailabilityGroupName of the source database and check that the database is not a relic, it means a dropped database:

#loop on all source databases
Foreach ($database in $databases)
{
    IF ([string]::IsNullOrEmpty($database.AvailabilityGroupName))
    {  
        #Find the database which is not part of an AAG
        $db = Get-RubrikDatabase -ServerInstance $sourceInstance.name -Database $database.name | Where-Object isrelic -eq $false;
    }
    ELSE
    {
        #Find database which is part of an AAG
        $db = Get-RubrikDatabase -AvailabilityGroupName $database.AvailabilityGroupName -Database $database.name | Where-Object isrelic -eq $false  #| Get-RubrikSnapshot -Latest
    }
}  

And now comes the tricky part of the restore, the creation of the new files structure which gave me some headaches…
Indeed our source database can have multiples Data and Log files and those files will need to be recreated in the new instance with the logical name, file name and the correct path.
Here a crucial point is to never change the logical name, it should be the same than the original one otherwise it’s not working.
We will need to retrieve first the database files for the source database and after construct an array of list which will build our new database files structure, in the meantime we give a new name to our destination database by adding the suffix Rest_:

#Change database name
$destDBName = 'Rest_' + $database.name;

#Find database files
$dbFiles = Get-DbaDbFile -SqlInstance $sourceInstance.name -Database $database.Name;

#construct new files structure
$targetfiles = @();
foreach ($dbFile in $dbFiles)
{
    $LogicalName = $dbFile.LogicalName; ##### Logical name should be the same as original !!!!! #####
    $newFileName = Split-Path $dbFile.PhysicalName -leaf;
    $newFileName = 'rest_' + $newFileName;
    IF ($dbFile.TypeDescription -eq 'ROWS')
    { 
        $exportPath = $InstPath.Data; }
    ELSE
    {   
        $exportPath = $InstPath.Log; }
    $targetfiles += @{logicalName=$LogicalName;exportPath=$exportPath;newFilename=$newFileName}
}

Our restore is now ready to be executed. We will here restore the source database to the latest recovery point found in Rubrik but of course another recovery date could be selected.
A loop which checks the status of the restore is used to have the possibility to wait the end of the current restore before doing the next one. This will give the possibility to run an integrity check for example after the restore and also to drop the database after that to let the destination instance on the same state than before this restore test.
To have a better visualization of the restore progression a progress bar is also added:

$Restore = Export-RubrikDatabase -id $db.id -recoveryDateTime (get-date((Get-RubrikDatabase -id $db.id).latestrecoverypoint)) -targetInstanceId $DestInstID.instanceId -targetDatabaseName $destDBName -TargetFilePaths $targetfiles -maxDataStreams 1 -FinishRecovery
            
#Loop until restore is finished
Do
{
    #Search status of the restore
    $status = (Get-RubrikRequest -id $Restore.ID -Type 'mssql'); 

    #show a progress bar for the restore
    Write-Progress -Activity Restoring -Status $status.status -PercentComplete $status.progress -CurrentOperation "Restore database $database"
} Until ($status.progress -ge 100);

#Restore done for the database
Write-Output "Restore is complete on database $destDBNam";

In this blog post I explained how to automate the restoration of multiples databases via Rubrik. I deliberately removed from my script the logging part as well as the integrity check after the restore followed by the dropped of the database. I will create another post for those parts.
I hope this post will help and I wish you an happy scripting!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Savorgnano
Stéphane Savorgnano

Senior Consultant