Restore a database backup to an Azure Managed Instance

1) Creating Azure storage account and upload backup file

-> Click “Create a resource”. Select “Storage” and click “Storage Account”.

Blog45_1.PNG

-> Complete “Create storage account” and click “Create”.

Blog45_2.PNG

-> Once the Storage Account deployment completes. Click on Resource group JB_RG, select jbmistorage. Under settings, click “Shared Access Signature”. Check the details and modify accordingly to your needs. I am leaving the default and clicking on”Generate SAS and connection string”.

Blog45_3.PNG

-> Copy the SAS Token and BLOB Service SAS URL and place it in carefully,

Blog45_4.PNG

-> Under “BLOB SERVICE”, click containers and Click “+ Container”.

Blog45_5.PNG

-> Provide the required details and click ok.

Blog45_6.PNG

-> Click on Resource group JB_RG, select jbmistorage. Click Conatiners under “BLOB SERVICE”. Click on “jbmibackupcontainer”. Click on properties.

Blog45_7.PNG

-> Copy the URL.

Blog45_8.PNG

-> Once the URL is copied, go back to the container page by clicking  on Resource group JB_RG, select jbmistorage. Click Conatiners under “BLOB SERVICE”. Click on “jbmibackupcontainer”. Click on “Upload”. Select the backup file you want to upload and click “Upload”. Wait for the upload to complete.

Blog45_9.PNG

-> Upload of backup file in progress.

Blog45_10.PNG

-> Upload completed.

Blog45_11.PNG

2) Restore the database JB_AQ on the Managed instance.

-> Create a SAS Credential using below query,

CREATE CREDENTIAL [https://<storage_account_name>.blob.core.windows.net/<container>]
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’
, SECRET = ‘<shared_access_signature_key_with_removed_first_?_symbol>’

https://<storage_account_name&gt;.blob.core.windows.net/ – Click on “Resource group” JB_RG and select “jbmistorage”. Copy the “Blob Service Endpoint” as indicated below and replace “https://<storage_account_name&gt;.blob.core.windows.net/” with the copied value.

Blog45_12.PNG

<container>- Click on “Resource group” JB_RG and select “jbmistorage”. Select “Container” under “BLOB SERVICE”. Copy the container name on the Right side. The container name in my case is “jbmibackupcontainer”.

Blog45_13.PNG

-> When you click on the container “jbmibackupcontainer”. You will be able to find the backup file uploaded.

WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’ – Will remain as it is.

<shared_access_signature_key_with_removed_first_?_symbol> – Replace this with the SAS Token that was stored earlier. Please note that you should remove the leading ? from the SAS Token.

Blog45_18.PNG

-> My command is as below,

CREATE CREDENTIAL [https://jbmistorage.blob.core.windows.net/jbmibackupcontainer]
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’
, SECRET = ‘sv=2017-11-09&ss=b&srt=sco&sp=rwdlac&se=2018-06-13T09:33:37Z&st=2018-06-13T01:33:37Z&spr=https&sig=AWlMTj6MZ0M4ictY2nBJ4%2BfVr0kx0RWfpFU1xlJ76FU%3D’

-> Execute the query,

Blog45_16.PNG

-> Below query checks the SAS Credential and backup validity,

RESTORE FILELISTONLY FROM URL =
https://<storage_account_name&gt;.blob.core.windows.net/<container>/<Backup_File>.bak’

https://<storage_account_name&gt;.blob.core.windows.net/ – Click on “Resource group” JB_RG and select “jbmistorage”. Copy the “Blob Service Endpoint” as indicated below and replace “https://<storage_account_name&gt;.blob.core.windows.net/” with the copied value.

Blog45_12.PNG

<container>- Click on “Resource group” JB_RG and select “jbmistorage”. Select “Container” under “BLOB SERVICE”. Copy the container name on the Right side. The container name in my case is “jbmibackupcontainer”.

Blog45_13.PNG

<Backup_File>.bak – Replace it with the backup file we uploaded. When you click on the container “jbmibackupcontainer” . You will be able to find the backup file uploaded.

Blog45_15.PNG

-> My command is as below,

RESTORE FILELISTONLY FROM URL =
https://jbmistorage.blob.core.windows.net/jbmibackupcontainer/JB_AQP_MI.bak&#8217;

-> Execute the query,

Blog45_17.PNG

-> Use the below query to restore the database JB_AQ in Managed Instance.

RESTORE DATABASE [JB_AQ] FROM URL =
https://jbmistorage.blob.core.windows.net/jbmibackupcontainer/JB_AQP_MI.bak&#8217;

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Physical_database_name column in sys.databases

-> I was trying to rename a database on the Azure managed Instance and got the below expected error,

TITLE: Microsoft SQL Server Management Studio
——————————
Unable to rename JB_MI_1. (ObjectExplorer)
——————————
ADDITIONAL INFORMATION:
Rename failed for Database ‘JB_MI’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMS_Rel_17_4).180502-0908)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Database&LinkId=20476
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Database ‘8b680fc7-b6ab-4d70-be7a-dff62547bf51‘ is enabled for database mirroring or has joined an availability group. The name of the database cannot be changed. (Microsoft SQL Server, Error: 957)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=957&LinkId=20476
——————————
BUTTONS:
OK
——————————

Blog44_1.PNG

-> It was unusual to see a GUID as a database name in the message.

-> I checked this further to see if I can understand what this GUID is and tried querying the sys.databases to see if I have some details.

-> The sys.databases has a column called physical_database_name that holds this value.

Blog44_2.PNG

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.