One-time complete backup/export of Azure SQL Database

Question

One-time complete backup/export of Azure SQL Database

Chemie Alliance 0

I am from a Finance and Accounts background and I am not technically trained. I would like some guidance on how to take a complete local backup of our Azure SQL Database in a simple, step by step manner.

The reason I am asking this is that although Azure provides its own backup and recovery mechanisms, I believe it is important for us to also maintain an independent local copy of our complete database at periodic intervals.

For example, if someone accidentally deletes or modifies some important data, or if data needs to be recovered after the available retention/recovery period, it may not always be possible to retrieve the older data. Having our own local copy would give us an additional level of protection and allow us to preserve historical data independently.

I would therefore like to understand the following:

1. Is it possible to take a complete local copy of an Azure SQL Database?

I am looking for a format that contains the complete database structure as well as all the data, and which can be restored/imported into Azure SQL or SQL Server in the future if required.

I have come across the term BACPAC. Would BACPAC be suitable for this purpose, or is there another/better format for a complete backup?

2. I would also like a readable copy of the data

Apart from the restorable database copy, I would like to periodically export the actual data into Excel/CSV or another commonly readable format, including:

• Customer masters • Vendor masters • Item/product masters • All transaction data • Other important master and transaction tables • Any other data stored in the database

The objective is to have both:

A. Restorable database copy For complete recovery of the database.

B. Readable data copy For independently reviewing/accessing the business data without having to connect to Azure.

3. What would be the cost?

Since this would be a periodic backup activity, I would like to understand:

• Whether exporting the database itself has any additional cost • Whether BACPAC export has any charges • Whether an Azure Storage Account is required • Whether downloading and storing the file locally has any charges • Any other costs involved

4. How can I do this step by step?

I would really appreciate instructions suitable for a non-technical person, starting from the Azure Portal.

For example:

Log in to Azure Portal

Locate the SQL Database

Check the database size

Create/select the required storage location

Export the complete database

Download and securely store the backup locally

Verify that the backup is complete

Export the tables/data into Excel or CSV

Verify that all important data has been exported

Explain how to test whether the backup can actually be restored

If additional software such as SSMS, SQLPackage or any other tool is required, please explain what it is, why it is required and how a non-technical user can use it.

5. Most importantly, how should we do this periodically?

I would also appreciate advice on what is considered a sensible approach for maintaining periodic local copies of an Azure SQL Database, for example monthly, quarterly or any other suitable frequency.

I am not looking to replace Azure's normal backup and recovery system. I am looking for an additional independent copy of our business data so that we are protected against accidental deletion, incorrect updates or the inability to recover older data after the applicable retention period.

If someone could explain the recommended approach in simple language and step-by-step instructions, it would be extremely helpful.

Thank you.

0 comments

1 answer

Your answer

Answer 1

For your scenario, I would separate db recovery, long-term retention, and business-data archiving because they solve different problems.

The key point is that a BACPAC is useful as an independent copy of your database, but it should not be viewed as a replacement for Azure SQL Database's built-in backup and recovery capabilities. Microsoft specifically states that BACPAC files are intended for portability, migration and archiving scenarios, not backup-and-restore operations.

1. Can I take a complete local copy of an Azure SQL Database?

Yes.

The most common approach is to export the database as a BACPAC file.

A BACPAC contains:

Database schema
Table data
Metadata required to recreate the database during import

It can later be imported into Azure SQL Database, Azure SQL Managed Instance, or SQL Server.

A useful point to understand is that Azure SQL Database does not expose its internal automatic backup files for download. If you want a customer-controlled copy outside Azure's managed backup system, a BACPAC export is the standard option.

2. What should I use for long-term recovery?

If your concern is recovering data months or years later, consider enabling Long-Term Retention (LTR) in addition to keeping independent exports.

LTR stores selected automated backups for up to 10 years and allows those backups to be restored as a new database. Azure SQL Database also supports immutable LTR backups for additional protection against accidental or malicious deletion.

Therefore:

PITR (Point-in-Time Restore) = short-term operational recovery
LTR = long-term Azure-managed recovery
BACPAC = independent archive/export copy

3. How do I create the independent copy?

Using Azure Portal:

Azure Portal → SQL Databases → Select Database → Export

You will be prompted for:

Storage account
Storage container
Authentication details

The export creates a .bacpac file in Azure Storage, which can then be downloaded and stored on company-controlled storage.

One recommendation: periodically import an archived BACPAC into a test database to confirm it can still be successfully restored.

4. What about Excel or CSV exports?

This is a separate requirement from database backup.

A BACPAC is intended for database reconstruction, not business review.

For finance, audit, or compliance purposes, it is often better to export important business tables separately, such as:

Customer master
Vendor master
Product master
Chart of accounts
Sales transactions
Purchase transactions
Inventory
Invoices
Payments

For large datasets, CSV is generally more practical than Excel because Excel has row limits and can become difficult to manage.

5. What costs are involved?

There is no separate "BACPAC licence fee".

Typical costs are:

Azure Storage used to hold the exported BACPAC
Storage used for any CSV exports
Any local storage where copies are retained
Potential network transfer costs depending on how the files are downloaded and stored

The export operation itself also consumes database resources while it runs.

6. Practical approach

A common pattern is:

Azure SQL automatic backups for day-to-day recovery
LTR for longer retention requirements
Periodic BACPAC exports for an independent customer-controlled archive
CSV exports of key business tables for finance and audit review
Periodic restore testing to validate archived copies

If the database becomes large, Microsoft recommends using SqlPackage rather than relying solely on portal-based export operations.

Bottom line: Use Azure SQL's built-in backup capabilities (PITR and, where required, LTR) for recovery, and use BACPAC exports plus selected CSV exports as your independent archive strategy. Do not treat BACPAC as a substitute for Azure SQL's native backup system.

Help make this community better for everyone: if this answer resolved your issue, please accept it or leave an upvote. If not, share more details in a comment so we can continue the discussion and find the right solution.