The more SQL Server instances we need to manage, the easier it gets relying on third-party tools to complete our daily tasks. However, this brings another problem, that is, integrating with any application through the interfaces they provide. A very common request is to perform a database restore, and the reasons are many: development, testing, address a production issue, view deleted data, disaster recovery, etc. If we knew where the backup files are located (full, diff and log), it's a matter of running T-SQL commands. If we have a data management provider we depend on their website and/or API to perform this task.

In the following example we're going to demonstrate this with Rubrik, thinking on scheduling it as a SQL Server Agent job, using Rubrik REST API and PowerShell v2.0 for maximum compatibility. We're not going to use the Rubrik module as it requires PowerShell v3.0 and needs to be installed in every machine where we want to perform the restore. We're also not going to use PowerShell Remoting as it requires installing a self-trusted security certificate between the machines where we want to perform the restore and a single machine with Rubrik PowerShell module, which in production is a security concern.

Gather the Required Information

Note this needs to be performed only once, then we can use fixed values to perform the restore. The first step is to authenticate with username and password to get a token:

Add-Type -Assembly System.ServiceModel.Web,System.Runtime.Serialization
$cluster_address = 'server.com.us1'
$user = 'youruser'
$password = 'yourpassword'
$uri = "https://$cluster_address/api/v1/session"
$request = [System.Net.WebRequest]::Create($uri)
$request.Headers.Add("Authorization", "Basic "+[System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes("$user`:$password")))
$request.Method = 'POST'
$response = $request.GetResponse()
$responseStream = $response.GetResponseStream()
$streamReader = New-Object System.IO.StreamReader($responseStream)
$string = $streamReader.ReadToEnd()
$response.Close()
$xml = New-Object System.Xml.XmlDocument
$xml.Load([System.Runtime.Serialization.Json.JsonReaderWriterFactory]::CreateJsonReader([byte[]][char[]]$string, [System.Xml.XmlDictionaryReaderQuotas]::Max))
$token = $xml.SelectSingleNode("//token").innerText

In the first line we load .NET libraries to work with XML and JSON. From lines 2 to 4 we need to enter our environment information. Lines 5 to 15 perform the POST request to Rubrik REST API and line 16 gets the token from the response. This token will be used with bearer authentication in the next steps.

Now we need to get the Rubrik host id based on host name, the variable value will be similar to Host:::f17001bb-c5f0-4eec-acc3-37d4021bbe9d:

$hostName = "DATABASESERVER1.com.us1"
$uri = "https://$cluster_address/api/v1/host?name=$hostName"
$request = [System.Net.WebRequest]::Create($uri)
$request.Headers.Add("Authorization", "Bearer " + $token)
$response = $request.GetResponse()
$responseStream = $response.GetResponseStream()
$streamReader = New-Object System.IO.StreamReader($responseStream)
$string = $streamReader.ReadToEnd()
$response.Close()
$xml = new-object System.Xml.XmlDocument
$xml.Load([System.Runtime.Serialization.Json.JsonReaderWriterFactory]::CreateJsonReader([byte[]][char[]]$string, [System.Xml.XmlDictionaryReaderQuotas]::Max))
$hostId = @($xml.GetElementsByTagName("id"))[0]."#text"

With the host id, we will get the instance id, we used this link for parsing the response, and the variable value will be similar to MssqlInstance:::d9700bf1-84f9-4d11-babd-6df7ae63f80f:

$instanceName = 'MSSQLSERVER'
$uri = "https://$cluster_address/api/v1/mssql/instance?root_id=$hostId"
$request = [System.Net.WebRequest]::Create($uri)
$request.Headers.Add("Authorization", "Bearer " + $token)
$response = $request.GetResponse()
$responseStream = $response.GetResponseStream()
$streamReader = New-Object System.IO.StreamReader($responseStream)
$string = $streamReader.ReadToEnd()
$response.Close()
$xml = new-object System.Xml.XmlDocument
$xml.Load([System.Runtime.Serialization.Json.JsonReaderWriterFactory]::CreateJsonReader([byte[]][char[]]$string, [System.Xml.XmlDictionaryReaderQuotas]::Max))
foreach ($instance in $xml.root.data.item) {
  if ($instance.name."#text" -eq $instanceName) {
    $instance_id = $instance.id."#text"
  }
}

With the instance id, we will get the database name, the variable value will be similar to MssqlDatabase:::cce29f30-947d-43bd-b72f-1ec8d6749ea9:

$databaseName = 'yourdatabase'
$uri = "https://$cluster_address/api/v1/mssql/db?instance_id=$instance_id&name=$databaseName"
$request = [System.Net.WebRequest]::Create($uri)
$request.Headers.Add("Authorization", "Bearer " + $token)
$response = $request.GetResponse()
$responseStream = $response.GetResponseStream()
$streamReader = New-Object System.IO.StreamReader($responseStream)
$string = $streamReader.ReadToEnd()
$response.Close()
$xml = new-object System.Xml.XmlDocument
$xml.Load([System.Runtime.Serialization.Json.JsonReaderWriterFactory]::CreateJsonReader([byte[]][char[]]$string, [System.Xml.XmlDictionaryReaderQuotas]::Max))
$db_id = @($xml.GetElementsByTagName("id"))[0]."#text"

And this is an extra step, we will get the oldest recovery point but we can specify the value we need, we used this link for the conversion, and the variable value will be 1581474853000:

$uri = "https://$cluster_address/api/v1/mssql/db/$db_id"
$request = [System.Net.WebRequest]::Create($uri)
$request.Headers.Add("Authorization", "Bearer " + $token)
$response = $request.GetResponse()
$responseStream = $response.GetResponseStream()
$streamReader = New-Object System.IO.StreamReader($responseStream)
$string = $streamReader.ReadToEnd()
$response.Close()
$xml = new-object System.Xml.XmlDocument
$xml.Load([System.Runtime.Serialization.Json.JsonReaderWriterFactory]::CreateJsonReader([byte[]][char[]]$string, [System.Xml.XmlDictionaryReaderQuotas]::Max))
$oldestRecoveryPoint = @($xml.GetElementsByTagName("oldestRecoveryPoint"))[0]."#text"
$ts=[Math]::Round(([DateTime]$oldestRecoveryPoint).ToFileTime()/10000000-11644473600)*1000

Restore the database: export with a different name

Once we have the required values, the following script performs the actual restore of the database, requesting an export using a POST action:

$uri = "https://$cluster_address/api/v1/mssql/db/$db_id/export"
$body = @"
{"recoveryPoint":{
   "timestampMs":$ts},
 "targetInstanceId":"$instance_id",
 "targetDatabaseName":"yourdatabase2",
 "targetFilePaths":[
   {"logicalName":"yourdatabase_Data","exportPath":"C:\\MSSQLSERVER\\Data","newFilename":"yourdatabase_2.mdf"},
   {"logicalName":"yourdatabase_Log","exportPath":"C:\\MSSQLSERVER\\Log","newFilename":"yourdatabase_2.ldf"}],
 "finishRecovery":true}
"@
$request = [System.Net.WebRequest]::Create($uri)
$request.Headers.Add("Authorization", "Bearer " + $token)
$request.Method = 'POST'
$requestStream = $request.GetRequestStream()
$streamWriter = New-Object System.IO.StreamWriter($requestStream)
$streamWriter.Write($body)
$streamWriter.Dispose()
$requestStream.Dispose()
$response = $request.GetResponse()
$responseStream = $response.GetResponseStream()
$streamReader = New-Object System.IO.StreamReader($responseStream)
$string = $streamReader.ReadToEnd()
$response.Close()

Note from lines 6 to 9 we need to specify the new database name and the file paths and datafile logical and physical names. In the Rubrik web site we can view the progress of this action, or we can continue using REST API to track this progress.

Conclusion

It doesn't matter the interfaces any third party application provide, we need to find ways to ease our work, and if they provide a REST API to interact with it, we can adapt the code demonstrated here to our specific needs and succeed.