How to create user-defined restore points on Azure SQL Data Warehouse

  • Article

Microsoft announced user-defined restore point on Azure SQL Data Warehouse.

This feature enables you to manually trigger snapshots to create restore points of your data warehouse before and after large modifications. This capability ensures that restore points are logically consistent which provides additional data protection in case of any workload interruptions or user errors for quick recovery time. User-defined restore points are available for seven days and are automatically deleted on your behalf. Only 42 user-defined restore points are supported at any point in time so they must be deleted before creating another restore point. You can trigger snapshots to create user-defined restore points through PowerShell.

Backup and restore in Azure SQL Data Warehouse.

/en-us/azure/sql-data-warehouse/backup-and-restore

You can follow up below simple steps to utilize user-defined restore point.

Create user-defined restore point

1) Run powershell to create user-defined restore point.

$SubscriptionName="<subscription id>"

$ResourceGroupName="adwdbrg"

$ServerName="adwsv"

$DatabaseName="adwdb"

$RestorePointLabel="adwdb_restorepoint"

Connect-AzureRmAccount

Get-AzureRmSubscription

Select-AzureRmSubscription -SubscriptionName $SubscriptionName

#Create Restore point

New-AzureRmSqlDatabaseRestorePoint -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -RestorePointLabel $RestorePointLabel

2) Sign in with admin account.

3) You can see this output in powershell window when restore point is created.

ResourceGroupName        : adwdbrg

ServerName               : adwsv

DatabaseName             : adwdb

Location                 : Central US

RestorePointType         : DISCRETE

RestorePointCreationDate : 6/26/2018 4:45:44 AM

EarliestRestoreDate      :

RestorePointLabel        : adwdb_restorepoint

Restore database

1) Open SQL Server Management Studio and connect to user database ‘adwdb’. Run below query to check user-defined restore point.

select *

from sys.pdw_loader_backup_runs

where name = 'adwdb_restorepoint'

you can see that submit_time is ‘2018-06-26 04:45:44.023’

2) Click ‘Restore’ button on the ‘adwdb’ SQL data warehouse in Azure Portal.

3) Change database name and choose ‘2018-06-26 04:45:44’ as Restore point (UTC). Click ‘OK’ to start restoring database.

4) New database (‘adwdb_restorepoint’) is created on same SQL Server.

Remove user-defined restore point

1) Run powershell to remove user-defined restore point.

$SubscriptionName="<subscription id>"

$ResourceGroupName="adwdbrg"

$ServerName="adwsv"

$DatabaseName="adwdb"

$RestorePointCreationDate = Get-Date "6/26/2018 4:45:44 AM"

Connect-AzureRmAccount

Get-AzureRmSubscription

Select-AzureRmSubscription -SubscriptionName $SubscriptionName

#Remove Restore point

Remove-AzureRmSqlDatabaseRestorePoint -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -RestorePointCreationDate $RestorePointCreationDate

2) Sign in with admin account.

3) You can see this output in PowerShell window when restore point is deleted.

ResourceGroupName        : adwdbrg

ServerName               : adwsv

DatabaseName             : adwdb

Location                 :

RestorePointType         :

RestorePointCreationDate : 6/26/2018 4:45:44 AM

EarliestRestoreDate      :

RestorePointLabel        :