Short answer - You can’t…
Medium answer - … and you don’t really need to for anything related to DR or HA. Backups are automagically handled in Azure, Azure SQL DB georeplication can be set up in a few button clicks. Instead of
thinking of Azure SQL DB as a traditional SQL database in a server, think of it as a platform,
consider spinning
up a stand-alone copy of the production Azure SQL DB in a cheaper tier to
set as pre-production. It’d be a better, truer dev/test/qa environment as a
result.
Long answers – Okay, so you really want to get the Azure SQL DB
to an on-prem SQL Server instance. Sigh.
- You can manually script it out into CREATE and INSERT T-SQL statements – schema and data – with various tools, including a compare with SSDT, or an officially supported free crossplatform scripter that is Python-based. Keep in mind this might be a huge file and a lengthy manual process with a large database.
- You could also use the “Export”
button on the Azure portal for the Azure SQL DB to create a BACPAC on an Azure storage account. The file is locked with a
username/password which you specify at the time of export. It will take a few minutes, even for a small database.
Once it's done, you can download the .bacpac file from the blob location with Azure
Storage Explorer or something similar, and then import the .bacpac. Keep in mind that this might also be a huge file, and a lengthy
manual process with a large database.
