After a data migration, we needed to decommission the old Azure SQL DBs, but we wanted to keep a copy in case we needed anything later. Enter exporting an Azure SQL DB to storage! Updated with new info I discovered as part of the export process.
Exporting
It’s very easy. It’s as simple as having a storage account and then exporting the db to it in the portal. Note: you will want a cheap storage option as you may never be accessing this data. Start by navigating to your db, then click Export.
Fill out the fields, choosing your storage account and your authentication type. Note: If you have any multifactor auth with Microsoft Entra, you won’t be able to export using that method. It’s the other authentication type available in addition to SQL Server. I’m using the admin login to avoid any authentication issues.
After clicking OK, you will see the backup in your storage container. It’s a bacpac file. It will give you an alert to tell you that it’s submitted to export the database.
Note: You probably want to put exported databases in cool access tier to save on costs. You have to configure this with the storage account.
Monitoring Your Export
It will place a file there regardless of whether it actually completes the export. The initial file size is 4B. You need to monitor the exports at the SQL Server level. There is a page for this called Import/Export history.
Make sure to keep an eye on the status because it’s not exported until it’s done in the Import/Export history. Just because a file is in the storage account doesn’t mean you are good to go.
I’ve had them fail in production because of inconsistencies in the schema such as ‘one or more supported elements were found in the schema used as part of a data package’ and it goes on to explain that a view ‘contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous.’ Your errors may vary. In the case of that error, I would have to fix or remove that view for the export to work.
Importing
Now, if you are like me, you would like to know how to import your export back into Azure SQL. It’s as simple as importing it back into an Azure SQL server. I also wanted to check if it actually backed everything up properly – or at least spot check that it did because the export files were really small.
On the import page, you need to select the bacpac file, and choose some options for it.
Once the import is done, you will see the db in the list of dbs.
And it’s accessible in Azure Data Studio.
I went ahead and checked my row counts for one of the prod exports I imported using this query, just to be sure the counts matched. They matched.
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY [TableName]
GO
It’s really very easy to export and import with Azure SQL DB!