Goal - Take a database backup in Azure and copy the backup file(s) to company network for archiving. Note that I'm not asking how to copy a database.

Research - I have knelt at the alter of Google, but have yet to come across any useful information on solving this particular problem.

Any ideas on what path I should explore?

  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Sep 7 '21 at 19:16

The question is a little vague; do you mean an Azure SQL Database or Azure SQL Managed Instance, or do you mean an Azure VM hosting a SQL Server instance?

Azure SQL Database or Azure SQL Managed Instance

You cannot take a normal backup of an Azure SQL Database or Azure SQL Managed Instance because you do not have access to the underlying filesystem, so the normal BACKUP commands are unavailable.

If you want to take normal backups of these databases, it is not possible.

What you can do is take an export to a BACPAC file, using either SSMS, SQLPackage.exe or the Azure Portal. SQLPackage.exe is the best option if you want to automate the process.

One caveat with BACPAC exports - these are not proper backups, they are simply an export of schema and data from the database at the point of export. When you import the BACPAC it creates a new database before importing the schema and data from the BACPAC.

They should not be used as a replacement for disaster recovery - for Azure SQL Database or Azure SQL Managed Instance, you should be using automated backups for disaster recovery for at least the short-term, then maybe using BACPAC exports for long-term retention.

Note that automated backups support long-term retention for up to 10 years, so it may be possible to leverage this without the need to bring anything back down to your on-premises network.

SQL Server Instance on Azure Virtual Machine

In this scenario, either you have a route to your on-premises network via a VPN or ExpressRoute, or not. If you do, then normal backup methods should suffice as you can simply backup directly to your network location.

Without a direct route from the VM to your on-premises storage location, you can backup to blob storage and download the file using AzCopy or PowerShell to download the backups.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.