Restore Multiple DB's from Azure Storage Account to SQL Managed Instance

Question

Post reply

Restore Multiple DB's from Azure Storage Account to SQL Managed Instance

MillionQueries

Old Hand

Points: 369
More actions
February 3, 2021 at 3:54 pm

#3840574

Hello all,
I am working on a Backup/Restore script directly from the Azure Storage Account.
1) Backup Multiple Databases from SQL Server into Azure Storage Account
2) Restore Multiple Databases from Azure Storage Account into SQL Managed Instance.
The Script to directly backup the databases into Azure Storage account works but I am having hard time figuring out how to restore the backed up files from Azure Storage account to SQL Managed Instance.
Example of my script is below -:
USE [master]
GO
RESTORE DATABASE [crs-portal-log-dev1] FROM URL =
N'https://sqlmistorageaccountpt.blob.core.windows.net/sqlmipt/BKP_crs-portal-log-dev_FULL_2021-01-29-1321.BAK'
GO
I would like "BKP_crs-portal-log-dev_FULL_2021-01-29-1321.BAK' " part to be directly captured in the cursor query. I can also send the entire script.
Appreciate your help. Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

I'm slightly confused here. The purpose of SQLMI is that MS handles backups and restores. There is automation to allow this, whether PITR or from a specific backup, but AFAIK, you should not be backing up to BLOB storage and restoring. You should use the MS APIs to restore to the same or different MI instance.

https://docs.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups#programmatic-recovery-using-automated-backups

MillionQueries Old Hand Points: 369 More actions · Answer 3

Thanks for your message @Steve.

I need this script for a Data Migration project from Azure VM to Azure SQL MI.

I already have a backup script which directly backs up 10 databases from SQL Server to Azure Storage Container.

I want to restore the those 10 DB's which have been backed up in storage container to the Azure SQL MI instance, that's why i am looking for a script which will pickup the files from Storage container and restore in SQL MI instance.

Thank you

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

This might help: http://www.nikoport.com/2020/08/06/restoring-sql-server-backup-files-from-azure-blob-storage/

MillionQueries Old Hand Points: 369 More actions · Answer 5

Thank you @steve-2, Appreciate your help

I have already done the restore using the URL by manually using the Restore commands mentioned in this article, however I am looking to automate this. For example -:

Source - Script to backup 5 databases directly into the Azure Storage container (Completed)
Restore - Script that will pickup all the above 5 backed up database backup files and run the restore on my SQL Managed instance.

I don't want to manually input the names of the backup files in the restore script, I would like the restore script to pick it up directly from the Azure Storage account. Something similar to below script which runs in a cursor and picksup latest backup files.

Instead of the backupset, would it be possible that it will run the cursor and pickup the backup files from the storage account and restore those files in SQL MI?

RESTORE DATABASE ['+DBNAME+'] FROM URL =

N'''+ @BKPATH+'/'+ RIGHT(BF.physical_device_name, CHARINDEX('\', REVERSE(BF.physical_device_name)) -1)+'''

GO' FROM #RESTORE_SQL RS

INNER JOIN msdb.dbo.backupset BS ON RS.DBNAME = BS.database_name

INNER JOIN MSDB.DBO.backupmediafamily BF ON BS.media_set_id=BF.media_set_id

WHERE DBNAME=@dbname AND BS.backup_set_id in (select max(backup_set_id) from msdb..backupset where type='D' group by database_name)

GROUP BY RS.DBNAME,BF.physical_device_name

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

I believe you could use PowerShell to do this. Connect with the Az PowerShell module (https://docs.microsoft.com/en-us/powershell/azure/new-azureps-module-az?view=azps-5.5.0) and use that to read the file names and locations. You can then use the names to determine which db.

chava.sreekanth SSC Journeyman Points: 98 More actions · Answer 7

Hi, MillionQueries, I am in the same situatio as you, Did you find the solution for automatic restore of databases from azure storage account?

Any help is appreciated.