How To Restore Azure SQL Database

by

Once I accidentally deleted my Azure SQL database, after my analysis, I identified a few approaches to restore it. In this Azure tutorial, we will discuss all the approaches to restoring the Azure SQL database.

How To Restore Azure SQL 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 it. To restore Azure SQL database, follow the steps below.

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

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

restore azure sql database from bak file

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

azure sql database restore

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 click the Review + Create button.

azure sql restore

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

How To Restore Azure SQL 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 a few parameters on the above script based on your needs.

Azure SQL Managed Instance

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

  1. Open the PowerShell ISE with Run as Administrator mode.
  2. 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 Azure SQL Database Using Azure CLI

Azure SQL database

Using the steps below, you can easily restore an Azure SQL database using Azure CLI.

You can actually create a new database by restoring it from a backup. 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

Using the script below, you can easily restore an Azure SQL Managed Instance database using Azure CLI.

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"

Restore Azure SQL Database from bak file

You can use the below SQL script to restore the SQL database


RESTORE DATABASE Database_Name FROM URL = 'https://demostoragesql5.blob.core.windows.net/mynewdb/TSINFO123.bak'
WITH CREDENTIAL = 'Name of your SQL credential',
MOVE 'Database' to 'D:\DATABASES\TSINFORestored.mdf',
MOVE 'Database_log' to 'M:\LOGS\TSINFORestored.ldf',
BLOCKSIZE = 512

Azure SQL Database Backup And Restore To Different Server

Here we will discuss Azure SQL Database Backup And Restore To Different Server. Follow the information below.

  1. Connect to the SQL Server Management Studio and locate the database.
  2. Right-click on the database name –> select the Tasks option, –> Click on the Generate Scripts option.
Azure SQL Database Backup And Restore To Different Server

3. From the left side, Click on the objects to export tab and select the database objects to