How to use Azure Data Factory to restore an Azure SQL Database from Azure Blob Storage

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!

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.

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).