Restore Synapse Dedicated SQL Pool Using PowerShell

We have seen how to clone the Synapse Dedicated SQL pool using the GUI in our blog https://techdiw.com/cloning-restoration-dedicated-sql-pool-formerly-sql-dw-in-synapse/. We can perform the same action using the PowerShell and make it more generic where it can be used for the Automation and can reuse the code to clone from any source to any target within the subscription.

As we saw in our last blog we can use either User Defined Restore Point or The Automatic Restore Point. Here in this blob also we will use the User Defined Restore Point to Restore the DB.

Note: The Restore command actually creates a new Database in the Target. So we have to provide the new Database name.

Steps we will follow to clone.

1. Connect to the Azure.
2. Select the subscription.
3. Create a User Defined Restore Point.
4. Get the created restore point and modify the Resource ID.
5. Restore the database using the details.

I have made the program to be user interactive so that it can be used by any person just by passing the values.

#We are getting every detail from user and making it interactive. We are storing the provided details into the #variables which we will pass as parameters in the cmdlet.

$subscription = read-host "Enter the Subscription"
$source_rg = read-host "Enter Source Resource Group"
$source_workspace = read-host "Enter source Synapse workspace"
$source_database = read-host "Enter Source Database name"
$target_newpool = read-host "Enter Target Database Name"
$restorepointname = read-host "Enter Restore Point Name"
$target_rg = read-host "Enter Target Resource Group"
$target_workspace = read-host "Enter the Target workspace name"

#This cmdlet will connect to azure using the logged credentials

Connect-AzAccount

#There may be situation where you are part of more than one subscription, this cmdlet will mention the list of #subscription in which you are in

Get-AzSubscription 

#Select the subscription in which we have the source and the target Pool

select-Azsubscription -SubscriptionName $subscription

#Here we are creating a restore point in the source Database by passing the parameters which we took from user

New-AzSynapseSqlPoolRestorePoint -ResourceGroupName $source_rg -WorkspaceName $source_workspace -Name $source_database -RestorePointLabel $restorepointname

#Once the Restore Point is created, we are taking the restore Point details and selecting only the details we #want to see

$restorepoint = Get-AzSynapseSqlPoolRestorePoint -ResourceGroupName $source_rg -WorkspaceName $source_workspace -Name $source_database | Select-Object RestorePointLabel, RestorePointCreationDate, Id, tags | Where-OBJECT {($_.RESTOREPOINTLABEL -eq $restorepointname)}

#Getting the source pool details for the ID

$sourcesqlpool = Get-AzSynapseSqlPool -ResourceGroupName $source_rg -WorkspaceName $source_workspace -Name $source_database

#The source database will return the resource ID with the synapse workspace detail so we need to make to the #database id by replacing the values in the URL

$DatabaseID = $sourcesqlpool.Id -replace "Microsoft.Synapse", "Microsoft.Sql" `
    -replace "workspaces", "servers" `
    -replace "sqlPools", "databases"

#This is just a checkpoint to verify the name of the correct source database. If you spell the database name #incorrectly then it will break flow and will not continue running

if ($sourcesqlpool.SqlPoolName -eq <"Mention your source Database name to have a checkpoint"> -and $sourcesqlpool.WorkspaceName -eq $source_workspace)
{
    write-host "Got Correct source Database." -ForegroundColor Green
}
else
{
    write-error -Message "Please check the source workspace and database name"
    return
}

#This is the cmdlet which will help us in restoring the DB.

$RestoredDatabase = Restore-AzSynapseSqlPool -FromRestorePoint -RestorePoint $restorepoint.RestorePointCreationDate -ResourceGroupName $target_rg -WorkspaceName $target_workspace -TargetSqlPoolName $target_newpool -ResourceId $DatabaseID -PerformanceLevel DW100c -Tag $sourcesqlpool.Tags

#This check will return if your database is restored correctly.

if($RestoredDatabase.status -eq "Online")
{
    write-host "The Database is restored successfully and is Online" -ForegroundColor Green
}

Always make sure the Restore-AzSynapseSqlPool will create new database in the workspace, it doesn’t overwrite the existing one.

The RestorePoint parameter takes the date to locate the restore point for restoration. So, we need to take the datetime of the restore point using the restorepoint.RestorePointCreationDate from the label.