Exporting and copying SQL Server backup files to Azure Blob Storage using Command Line

ByAnkush Jain| Last Updated On Saturday, Jun 05, 2021

Azure SQL Server Azure SQL Database

Disclaimer: I am a consultant at Amazon Web Services, and this is my personal blog. The opinions expressed here are solely mine and do not reflect the views of Amazon Web Services (AWS). Any statements made should not be considered official endorsements or statements by AWS.

In this post, we will understand how can to export the SQL Server database via the command line and copy it to Azure Blob storage.

Problem Statement & Solution

It is a very common practice to schedule daily nightly backups of the SQL Server database to prevent the application's data from any hardware failure or any other human-made error.

In this post, we will be storing these automated backup files in your Azure Storage account. Storing data in an Azure Storage account is cheap as compared to the hard disk that we purchase. Also, Azure is more reliable as your data is stored in the cloud and you can access it at any time.

So, now let's start.

Export SQL Server database using Command Line

Our first task is to export the database .bacpac file for our SQL Server database.

For this, follow the below steps:

Step 1: Locate SQLPackage.exe file

Locate the SQLPackage.exe file on your system. On my system, it was located at C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin.

If it is not on your system, you can download and install it. Click here to Download SQLPackage.exe

Step 2: Export the database to a .bacpac file

Run the below command to export the database into a .bacpac file.

cd "C:"
cd "C:\Program Files\Microsoft SQL Server\150\DAC\bin"
sqlpackage.exe /Action:Export /SourceServerName:.\SQLEXPRESS /SourceDatabaseName:InventoryDB /TargetFile:D:\DatabaseBackups\InventoryDB.bacpac

All parameters are self-explanatory.

Copy .bacpac file to Azure Blob Storage

Now, to copy the files to Azure Blob Storage, we will follow the below steps.

Step 1: Install AzCopy

AzCopy is a command-line utility that you can use to copy blobs or files to or from a storage account. 👉 Click here to download AzCopy

After downloading, copy it somewhere on your system. I copied it inside the C:\azcopy directory.

Step2: Prepare Azure Storage account

Before you copy the files to Azure, make sure you are done below steps:

Create Storage Account
Create a Blob Container
Generate a SAS Token for the Blob Container (With required permissions)

Step 3: Copy the .bacpac file using AzCopy

Run the below command to copy the .bacpac file to the Azure Storage account.

cd "C:"
cd "C:\azcopy"
azcopy.exe copy "D:\DatabaseBackups\InventoryDB.bacpac"  "https://[account].blob.core.windows.net/[container]/[path/to/blob]?[SASToken]"

That's all.