In this Azure tutorial, we will discuss the quick steps to restore Azure SQL database.
Table of Contents
- How To Restore Azure SQL Database Using Azure Portal
- How To Restore Azure SQL Database Using PowerShell
- How To Restore Azure SQL Database Using Azure CLI
- Restore Azure SQL Database from bak file
- Azure SQL Database Backup And Restore To Different Server
- How To Perform Point-in-time restore Using Azure portal
- FAQs
- Wrapping Up
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.
- 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 created earlier. Click on the specific Azure SQL server where you want to restore the Azure SQL database.
4. On the specific SQL server page, click on the Deleted databases from the left navigation under the 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 click the Review + Create button.
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
- 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 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.
- Connect to the SQL Server Management Studio and locate the database.
- Right-click on the database name –> select the Tasks option, –> Click on the Generate Scripts option.
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.
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.
- Connect to the Destination SQL Server Management Studio.
- Create a new database with the same database name as the source.
- 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
- Log in to the Azure Portal (https://portal.azure.com/).
- Search for the SQL databases and click on the search result SQL databases.
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.
4. Click the Restore button on the Overview tab as highlighted below.
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.
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.
- Log in to the Azure Portal (https://portal.azure.com/).
- Search for the Managed databases and click on the search result Managed databases.
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 !!!
I am Rajkishore, and I have over 14 years of experience in Microsoft Azure and AWS, with good experience in Azure Functions, Storage, Virtual Machine, Logic Apps, PowerShell Commands, CLI Commands, Machine Learning, AI, Azure Cognitive Services, DevOps, etc. Not only that, I do have good real-time experience in designing and developing cloud-native data integrations on Azure or AWS, etc. I hope you will learn from these practical Azure tutorials. Read more.