How To Revert SQL Azure Database

Well, here we will discuss how to revert SQL Azure database or how to recover SQL Azure database. Many times, we will have the requirement to revert our Azure SQL database or to recover the Azure SQL database if by chance we have lost the Azure SQL Database.

What about Recovery Time?

Note, that the recovery time of the Azure SQL database depends on a few key factors like the size and compute size of the DB, the total number of transaction logs that you are using, the network bandwidth, etc.

You can easily recover the Azure SQL database using the automated database backup option. Using the automated database backup options, you can perform the below scenarios

You can create a brand new Azure SQL database on the same server with a specified time within the retention period of time or the deletion time.
You can also create a brand new database in a different server in that particular region, that is recovered to the point of the latest backups.
Another option is to create a new Azure SQL DB in a different server in another region that is recovered to the point of the latest replicated backups.

Another important point to note down here is, it is not possible to overwrite an existing database at the time of restore operation.

If your Azure SQL database size is more, then it is definitely going to take a few hours to finish the complete database restore operation.

What is Point-in-time restore?

With the help of the point-in-time-restore process, you can able to restore your Azure SQL database or Azure SQL instance database to an earlier point in time.

You can perform the point-in-time-restore process by using the Azure Portal, or PowerShell, or the Rest API. The restored database is the replacement of the new database and the point-in-time-restore process basically creates a brand new database like the original database on the same server.

You need to pay for the complete restore process based on the service tier that you have and the compute size.

You can use the ALTER DATABSE command to rename the restored database the same as the original Azure SQL database.

If you want to retrieve the data from the restored database, you can just write and run a data recovery script that actually helps you to extract the data from the restored database and applies to the original database.

How To Perform Point-in-time restore Using Azure portal

You can easily perform the Azure SQL Database and the Azure SQL instance database using the below quick steps.

Azure SQL Database

Log in to the Azure Portal (https://portal.azure.com/).
Search for the SQL databases and click on the search result SQL databases.

How To Perform Point-in-time restore Using Azure portal

3. On the SQL databases window, you will see the lists of Azure SQL databases you have. Click on the Azure SQL database that you want to restore.

How To create Point-in-time restore Using Azure portal

4. Now Click on the Restore button on the Overview tab as highlighted below.

How To create Point-in-time restore in Azure portal

5. Then, on the Create SQL Database – Restore database window, provide the below details

Select source: Select the source as Point-in-time.
Restore point (UTC): Choose the restore point and time from when the new database will get created.
Database name: Provide a unique name for the database.
Want to use SQL elastic pool?: You can choose the Yes or No option based on your requirement.
Compute + Storage: You need to carefully choose the storage option based on your requirement. You can click on the configure database link to change the storage option based on your need.

Now, Click on the Review + create button.

how to restore deleted database in sql server

6. Now Finally, click on the Create button that you can see on the next window.

Azure SQL Managed Instance

You can follow the below steps to Perform Point-in-time restore in the Azure SQL Instance database Using the Azure portal.

Log in to the Azure Portal (https://portal.azure.com/).
Search for the Managed databases and click on the search result Managed databases.

How To Perform Point-in-time restore Azure portal

3. On the Managed databases page, you can able to see the lists of managed instances that you have created under your current Azure subscription, click on the SQL managed instance that you want to restore.

4. Once, you will click on the specific Azure SQL managed database, on the Overview tab, you can click on the Restore button to perform the Point-in-time restore Using the Azure portal.

How To Restore A Deleted Database Using Azure Portal

If by chance, you have deleted an Azure SQL database and want to revert it then it’s quite easy to restore Azure SQL database using the below steps.

Log in to the Azure Portal (https://portal.azure.com/).
Search for SQL servers and then click on the search result SQL servers.

3. Now, on the SQL servers page, you will find the lists of Azure SQL servers that you have created earlier. Click on the specific Azure SQL server where you want to restore the Azure SQL database.

How To Restore Deleted Database Using Azure Portal

4. On the specific SQL server page, click on the Deleted databases from the left navigation under Data management node as shown below.

5. You will see the list of databases that have been deleted before. Click on the particular database that you want to restore. Then on the Create SQL Database – Restore database page, Provide the Restore Point and the Database name and then click on the Review + Create button.

6. Finally, on the next window, click on the Create button.

This is How To Restore A Deleted Database Using Azure Portal.

How To Restore A Deleted Database Using PowerShell

You can also able to restore a deleted Azure SQL database using PowerShell. You can follow the below steps.

Azure SQL Database

In case, you want to restore a deleted Azure SQL database, you can follow the below steps.

Open the PowerShell ISE with Run as Administrator mode.
Run the below PowerShell script now.


$MySubscriptionId = ''
$MyresGroupName = "myResourceGroup-$(Get-Random)"
$region = "westus2"
$adminSqlLogin = "SqlAdmin"
$Adminpassword = "ChangeYourAdminPassword"
$serverName = "server-$(Get-Random)"
$MydatabaseName = "myDemoDatabase"
#Provide a new name for the restored DB
$NewpointInTimeRestoreDatabaseName = "MyDemoDatabase_15MinutesAgo"
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"

Set-AzContext -SubscriptionId $MySubscriptionId 

# Creating a resource group
$myresourceGroup = New-AzResourceGroup -Name $MyresGroupName -Location $region

$myserver = New-AzSqlServer -ResourceGroupName $MyresGroupName `
    -ServerName $serverName `
    -Location $region `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $Adminpassword -AsPlainText -Force))

$firewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $MyresGroupName  `
    -ServerName $serverName `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp

$mydatabase = New-AzSqlDatabase  -ResourceGroupName $MyresGroupName`
    -ServerName $serverName `
    -DatabaseName $MydatabaseName `
    -RequestedServiceObjectiveName "S0" 

Start-Sleep -second 600

Restore-AzSqlDatabase `
      -FromPointInTimeBackup `
      -PointInTime (Get-Date).AddMinutes(-10) `
      -ResourceGroupName $MyresGroupName `
      -ServerName $serverName `
      -TargetDatabaseName $pointInTimeRestoreDatabaseName `
      -ResourceId $mydatabase.ResourceID `
      -Edition "Standard" `
      -ServiceObjectiveName "S0"

Don’t forget to change few parameters on the above script based on your need.

Azure SQL Managed Instance

You can also able to restore a deleted Azure SQL Managed Instance database using PowerShell. You can follow the below steps

Open the PowerShell ISE with Run as Administrator mode.
Run the below PowerShell script now.

$MysubscriptionId = "<Your Subscription ID>"
Get-AzSubscription -SubscriptionId $MysubscriptionId
Select-AzSubscription -SubscriptionId $MysubscriptionId

$MyresGroupName = "<Provide the name of your Resource group>"
$MymanagedInstanceName = "<Provide the name of your SQL Managed Instance>"
$MydeletedDatabaseName = "<Provide the name of your Source database>"
$MytargetDatabaseName = "<Provide the name of your target database>"

$MydeletedDatabase = Get-AzSqlDeletedInstanceDatabaseBackup -ResourceGroupName $MyresGroupName `
-InstanceName $MymanagedInstanceName -DatabaseName $MydeletedDatabaseName

Restore-AzSqlinstanceDatabase -FromPointInTimeBackup -Name $MydeletedDatabase.Name `
   -InstanceName $MydeletedDatabase.ManagedInstanceName `
   -ResourceGroupName $MydeletedDatabase.ResourceGroupName `
   -DeletionDate $MydeletedDatabase.DeletionDate `
   -PointInTime UTCDateTime `
   -TargetInstanceDatabaseName $targetDatabaseName

How To Restore A Database Using Azure CLI

Azure SQL database

You can easily able to restore an Azure SQL database using Azure CLI using the below steps.

You can actually, create a new database by restoring from a back up. You can use the below script to restore the Azure SQL database

az sql db restore --dest-name
 [--auto-pause-delay]
[--backup-storage-redundancy]
[--capacity]
[--compute-model {Provisioned, Serverless}]
[--deleted-time]
[--edition]
[--elastic-pool]
[--family]
[--ha-replicas]
[--ids]
[--license-type {BasePrice, LicenseIncluded}]
[--min-capacity]
[--name]
[--no-wait]
[--read-scale {Disabled, Enabled}]
[--resource-group]
[--server]
[--service-objective]
[--subscription]
[--tags]
[--time]
[--zone-redundant {false, true}]

Example: Below is an example that we can consider here.

az sql db restore --dest-name MyDestination --edition GeneralPurpose --name MyDemoAzureSQLDB --resource-group MyDemoResGroup --server myserver --subscription MyNewSubscription --time "2021-04-20T06:34:22"

Azure SQL Managed Instance

You can easily able to restore an Azure SQL Managed Instance database using Azure CLI using the below script.

az sql midb restore --dest-name
 --time
[--deleted-time]
[--dest-mi]
[--dest-resource-group]
[--ids]
[--managed-instance]
[--name]
[--no-wait]
[--resource-group]
[--subscription]

Example: We can consider the below example here

az sql midb restore -g mydemogroup --mi myinstance -n mydemomanageddb --dest-name mytargetmidb --time "2021-04-20T07:34:25" --deleted-time "2021-04-20T07:34:25"

FAQs

After how many days of unsubscribing from SQL Azure account the database gets deleted

The answer to this question is 7 days.

Azure SQL Restore Database Overwrite

An important point to note down here is that during the database restore process, it is impossible to overwrite any existing database.

You may also like following below articles

Wrapping Up

In this article, we have discussed How To Revert SQL Azure Database, What is Point-in-time restore? How To Perform Point-in-time restore Using Azure portal, How To Restore A Deleted Database Using Azure Portal. Along with this, we have also discussed How To Restore A Deleted Database Using PowerShell, How To Restore A Database Using Azure CLI. Hope you have enjoyed this article !!!