How to create user-defined restore points on Azure SQL Data Warehouse
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 :