0

I have a vendor who dumps a .bak of our database to their SFTP server each night. I'm using ADF to copy that file each day to Azure Blob Storage. I'd like to be able to restore that database from the blob-stored .bak file to an Azure SQL Database in the same resource group. Is this possible?

If not, please hit me with alternate suggestions in a similar technology stack. The only thing that I can't change is the fact that all I have to work with is .bak files on an SFTP server. I do not have direct access to the original SQL Server database or server. The only access I have is through those .bak files, and I need to do a daily restore on the Azure SQL Database.

I've tried seeking out methods to achieve this, but the only help I'm getting from google is posts from many years ago saying that it can't be done. I'm hoping that's changed or that someone has a workable solution!

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
buktseat
  • 11
  • 4

1 Answers1

-1

Azure SQL Database does not allow restores from native SQL Server (IaaS or on-premises backups - .BAK extension). On Azure SQL you can "restore" by importing a bacpac only. Try to make your vendor to generate a bacpac instead of a native backup .BAK file. Alternatively, you can restore the .BAK on an Azure VM with SQL Server installed and there export the database as a bacpac and then you can import it (restore it) to an Azure SQL database.

If the Azure SQL Database you mention is in reality an Azure SQL Managed Instance that is a PaaS option that can handle .BAK restores, then yes you can automate the restore with Azure Data Factory (ADF), you can create a Script activity in Azure Data Factory to make the restore with a SQL statement as below:

RESTORE DATABASE [TheDatabaseName] FROM URL = N'https://theblobname.blob.core.windows.net/container/dbname.bak'

Please find the below screenshot, select Script Activity and use the linked service for the Managed Instance and place the .bak files in the Storage account and use that path in the above RESTORE statement.

enter image description here

The .bak will be restored once the script activity is executed. If you need to handle native backups the Azure SQL Managed Instance makes more sense than an Azure SQL Database (vCore or DTU model).

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • That's great advice, thanks for the thorough response. Do you have experience with the cost difference (in practical application, all things being equal) between Azure SQL vs. Managed Instance? As in, does it represent a big change in the dollars to go that direction instead? – buktseat Jul 10 '23 at 15:00
  • If you are using Azure SQL DTU-Model you don't pay for licensing as you may be able to see on Azure SQL Pricing Calculator, on Azure SQL Managed Instance (MI) you pay for licensing and may get some discounts with the Hybrid Benefit. Pricing for a General Purpose tier From $0.5044/hour for 4 vCores with 20GB RAM to $20.18/hour for 80 vCores with 396GB RAM. The important thing with managed instance is the amount of RAM and TempDB available depends on the vcores assigned to the instance. If your SQL processes and memory intensive, MI could be costly. Large databases do not perform well on GP. – Alberto Morillo Jul 10 '23 at 15:36
  • Thanks again! The database in question (the .bak file, at least) is about 15GB and the restored database in Azure would only be used for fairly simple reads for Power BI reporting purposes. One more question... If I'm able to get the vendor to export a bacpac instead of a .bak file, what's the mechanism/flow for restoring that bacpac daily to an existing Azure SQL Database using ADF? Or is there a better way than ADF to automate that? – buktseat Jul 11 '23 at 17:55
  • You can use Azure Automation https://stackoverflow.com/questions/58910291/daily-import-and-export-azure-sql-database – Alberto Morillo Jul 11 '23 at 19:19
  • However Azure SQL has a limit of 4 GB for databases. Azure Managed Instance has a limit of 16 GB on General Purpose. That said consider then using Azure Hyperscale. Hyperscale has a limit of 100 GB for database size. – Alberto Morillo Jul 11 '23 at 19:23
  • Ugh! So my original recommendation to my client (to use Azure SQL Database) was essentially misinformed as I can't do that with databases larger than 4GB? Seems like Hyperscale is the only option that would actually work given all the variables? I was under the impression that Standard Tier Azure SQL Databases were limited to 250GB! Does the cost go way up with hyperscale relative to Azure SQL Database? – buktseat Jul 11 '23 at 20:07
  • Now that Hyperscale has Serverless available (maybe is a fit in your scenario) and you can automatically scale up and down to reduce costs. Hyperscale is built on faster hardware. Backups will be faster. Here I found some estimated costs https://abcloudz.com/services/azure-sql-database-hyperscale/#:~:text=Hyperscale%20automatically%20adds%20a%20new%20page%20server%20when,expensive%20offer%20for%20this%20kind%20of%20data%20storage. Try playing a little bit with Azure SQL Calculator (service tier Hyperscale) https://azure.microsoft.com/en-us/pricing/details/azure-sql-database/single/ – Alberto Morillo Jul 11 '23 at 20:23
  • https://devblogs.microsoft.com/azure-sql/autoscaling-with-azure-sql-hyperscale/ – Alberto Morillo Jul 11 '23 at 20:24
  • Just heard back from the vendor - the .bak files are the best/only option I have. Looks like I'm going to have to go with the managed instance approach. Thank you so much for engaging with me on this! – buktseat Jul 11 '23 at 20:53
  • Please read my answer here about performance and backups on Azure Managed Instances. https://learn.microsoft.com/en-us/answers/questions/1300847/azure-sql-server-on-managed-instance-not-the-best. I guest that will tell you Hyperscale is probably best option – Alberto Morillo Jul 11 '23 at 20:53
  • Max Instance Size Managed Instance https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits?view=azuresql#service-tier-characteristics – Alberto Morillo Jul 11 '23 at 21:04
  • I've finally gotten around to setting up the managed instance and employing your suggestions. However, when trying to read the bak file from the storage account I keep getting "Operating system error 86(The specified network password is not correct.). RESTORE DATABASE is terminating abnormally." This despite the fact that I've set the storage account up with a public endpoint. I feel like I'm missing some minor detail with regard to permissions. Any idea what that might be? – buktseat Aug 02 '23 at 19:56
  • Please make sure you are trying to use a container on the storage account instead of a File Share on the Storage Account. – Alberto Morillo Aug 02 '23 at 20:22
  • I am indeed storing the file in a container, and trying to access it through the script operation. I believe I have it set up exactly as you suggested? – buktseat Aug 08 '23 at 13:03
  • Strange. I have seen that error with file shares but not with containers. Is it a General Purpose account v2 right? – Alberto Morillo Aug 08 '23 at 13:16
  • Yes, it's gp v2. – buktseat Aug 08 '23 at 15:22
  • Is there some extensive finagling I have to do to allow access to the blob from the SQL Managed Instance? – buktseat Aug 08 '23 at 15:25
  • I have not faced this type of error with Azure SQL backups to Azure Storage containers, maybe is worthy asking on Azure Storage account tag about this error, maybe one of the storage accounts experts there can help us. – Alberto Morillo Aug 08 '23 at 20:00
  • I figured that part out - I needed to establish an SAS token to allow access from the managed instance to the blob. – buktseat Aug 09 '23 at 18:19
  • But now I'm getting "The media family on device 'https://xxxx.blob.core.windows.net/xxxx/test2.bak' is incorrectly formed. SQL Server cannot process this media family. RESTORE DATABASE is terminating abnormally." My research indicates that this error is common when trying to restore to an older version of SQL server, but a managed instance shouldn't have this problem, right? – buktseat Aug 09 '23 at 18:21