Hello Readers!
Welcome to my new blog on performing a point-in-time restore of a database in an azure managed instance. In this small blog, I will be documenting the steps and screens for restoring a database in a managed instance using Azure Portal.
If you have not read about my blog on managed instance automated backups, please read it here.
Restoring automated backup with Azure portal
Step 1 : Navigate to your database in azure managed instance in the portal
Search for SQL managed Instance in the portal, Select the managed instance which has the database you are trying to restore. We would get to see the list of databases hosted in the instances, clicking on any of those can launch the database page in the Azure portal.
Click on restore in this page.
Step 2: Supplying the restore point details
In the popup that appear, you can mention the target database name . It has to be different from the database of which the backup was taken. If you keep the same name, the restore will fail with the error 'Database with the same name already exists on this Managed Instance.'
In the Restore point section against the data and time, you can choose a restore point which is within the short term retention plan period set for the instance. For directions, the portal also shows the earliest (most recent) restore point below.
In this window , again we would not get to change the Target Server. (MI). You can see the lock symbol against it .
Once you have made the choice click on Ok, which will initiate the restoration activity to the same instance.
You can track the progress through the below T-SQL statement in SSMS
Select percent_complete from sys.dm_exec_requests where session_id=<Spid of the session you get from sp_who2 active>
Using Powershell
Both the Azure modules AZ and AzureRM has commands for Database restoration.
If you dont have them installed in your Powershell client, you can also use the 'Cloud shell', which is the powershell client you will get to see in the Azure portal itself. Cloud shell basically uses the latest powershell Module, which is Az.
You can launch them using the button highlighted in the below snapshot.
Az Module powershell Script Sample
Restore-AzSqlinstanceDatabase -Name "DBA" -InstanceName "<yourMIname>" -ResourceGroupName "<ResourceGroupName>" -PointInTime “2019-07-29T08:51:39.3882806Z” -TargetInstanceDatabaseName "DBA_PSrestored"
If you are new to powershell and trying to execute the above powershell script, follow the below steps before you attempt this.
1.Run your powershell client as Administrator
2.Install Az module by running the below
Install-Module -Name Az
3. Login to your azure account with the below command
Connect-AzAccount
Welcome to my new blog on performing a point-in-time restore of a database in an azure managed instance. In this small blog, I will be documenting the steps and screens for restoring a database in a managed instance using Azure Portal.
If you have not read about my blog on managed instance automated backups, please read it here.
Restoring automated backup with Azure portal
Step 1 : Navigate to your database in azure managed instance in the portal
Search for SQL managed Instance in the portal, Select the managed instance which has the database you are trying to restore. We would get to see the list of databases hosted in the instances, clicking on any of those can launch the database page in the Azure portal.
Click on restore in this page.
Step 2: Supplying the restore point details
In the popup that appear, you can mention the target database name . It has to be different from the database of which the backup was taken. If you keep the same name, the restore will fail with the error 'Database with the same name already exists on this Managed Instance.'
In the Restore point section against the data and time, you can choose a restore point which is within the short term retention plan period set for the instance. For directions, the portal also shows the earliest (most recent) restore point below.
In this window , again we would not get to change the Target Server. (MI). You can see the lock symbol against it .
Once you have made the choice click on Ok, which will initiate the restoration activity to the same instance.
You can track the progress through the below T-SQL statement in SSMS
Select percent_complete from sys.dm_exec_requests where session_id=<Spid of the session you get from sp_who2 active>
Using Powershell
Both the Azure modules AZ and AzureRM has commands for Database restoration.
If you dont have them installed in your Powershell client, you can also use the 'Cloud shell', which is the powershell client you will get to see in the Azure portal itself. Cloud shell basically uses the latest powershell Module, which is Az.
You can launch them using the button highlighted in the below snapshot.
Az Module powershell Script Sample
Restore-AzSqlinstanceDatabase -Name "DBA" -InstanceName "<yourMIname>" -ResourceGroupName "<ResourceGroupName>" -PointInTime “2019-07-29T08:51:39.3882806Z” -TargetInstanceDatabaseName "DBA_PSrestored"
If you are new to powershell and trying to execute the above powershell script, follow the below steps before you attempt this.
1.Run your powershell client as Administrator
2.Install Az module by running the below
Install-Module -Name Az
3. Login to your azure account with the below command
Connect-AzAccount
Thanks for reading ! Please comment below for any query you have related to this topic.
I will write my next blog on Geo-restore !
No comments:
Post a Comment