Restore Azure SQL Database

How To Revert SQL Azure Database

In this Azure tutorial, we will discuss the quick steps to restore 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 an Azure SQL database using the below steps.

  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 database

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 script option. You can select the script, the entire database, and all database objects or select specific objects based on your need and then click the Next button.

4. Now, select the option to save the script, like save the script to file, clipboard, new query window, etc, on the Set scripting options window.

import the local SQL server database to Azure

5. Now, if you want, you can set the advanced scripting options also.

6. Click on the Advanced button and set the Script for the database engine type property to SQL Database if you don’t want the stand-alone SQL Server instance. Then, you need to click on the Next button.

7. Then, set the Types of data to script option to Schema only, Data only, Schema, and data based on your requirement.

8. Finally, click on the Finish button. Then it will generate the script on the local path provided by you.

Restore To a Different Server

Now to restore it to a different server, Follow the below steps.

  1. Connect to the Destination SQL Server Management Studio.
  2. Create a new database with the same database name as the source.
  3. Select the newly created database and run the above-generated script to restore the 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

  1. Log in to the Azure Portal (https://portal.azure.com/).
  2. 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. Click 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 be 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 requirements.
  • Compute + Storage: You must choose the storage option based on your requirements. 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. Finally, click on the Create button that you can see in 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.

  1. Log in to the Azure Portal (https://portal.azure.com/).
  2. 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 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 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.

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 is that during the database restore process, it is impossible to overwrite any existing database.

What is Point-in-time restore?

With the help of the point-in-time restore process, you can restore your Azure SQL or Azure SQL instance databases to an earlier 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 your service tier and the compute size.

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

Suppose you want to retrieve the data from the restored database. In that case, you can write and run a data recovery script that helps you extract the data from the restored database and applies to the original database.

You may also like the 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 !!!