Gunnar Peipman – Programming Blog

ASP.NET Core, Blazor, .NET, Azure, SharePoint, IoT

SQL Server
Azure Data platform 

SQL Server database backup to Azure Blob Storage

Gunnar Peipman 145 Views 1,201 Comments

SQL Server supports exporting data-tier applications (BACPAC). It means that database is packaged to one file with schema and data. It’s not same as SQL Server backups but to backup smaller databases it works pretty well. Those who don’t want to mess with local backup storage can use cloud services like Azure Blob Storage to keep database backups. Here’s the example how I automated backup of one not so big database using free tools SqlPackage and AzCopy.

Tools

To make data-tier application or BACPAC style backup of database and upload it to Azure Blob Storage we need two utilities:

  • SqlPackage – create BACPAC file of given database.
  • AzCopy – command-line utility to manage Azure Blob Storage

To keep things simple and minimalistic I downloaded both utilities as zip-files so I can keep all stuff in one folder. To update utilities I don’t have to uninstall them first – I just download newer zip-files and write files over.

Here’s how I organized files on disk.

Backup folder on SQL Server system disk

As SqlPackage comes with number of files I put it in separate directory so I don’t have any mess in my backup scripts directory.

Backup script

We will use simple DOS batch file to get work done. This is what our backup script will do:

  1. Define variables for:
    1. Azure Blob Storage container URL
    2. Storage container Shared Access Signature (SAS) token. If you don’t know how to generate SAS token then here’s the official guide by Microsoft: Manage blob containers using the Azure portal (a little bit before middle part of page).
    3. Date based name for backup.
    4. Database connection string.
  2. Bump database to local BACPAC file.
  3. Upload file to Azure Blob Storage container.
  4. Delete local BACPAC file.

Here’s the script with explaining comments. The only tricky part is getting the date right.

REM Define variables for connection string, storage URL and SAS token
set "CONNSTR=Server=tcp:localhost,1433;Database=db;User ID=X;Password=X;Trusted_Connection=False;Encrypt=False"
set STORAGE=https://my.blob.core.windows.net/container
set "SAS=YOUR_SAS_TOKEN_HERE"
 
REM Create date based file name
REM ~6,4 means from sixth character take next four
REM Correct date parts depend on date format in your system
set CUR_YYYY=%date:~6,4%
set CUR_MM=%date:~3,2%
set CUR_DD=%date:~0,2%
set FILENAME=%CUR_YYYY%%CUR_MM%%CUR_DD%.bacpac
 
REM Bump database to BACPAC file
SqlPackage\sqlpackage.exe /TargetFile:%FILENAME% /Action:Export /SourceConnectionString:%CONNSTR%
 
REM Copy BACPAC to Azure Blob Storage
Azcopy copy %FILENAME% "%STORAGE%?%SAS%"
 
REM Delete local BACPAC file
del %FILENAME% /Q

You can take this script with copy and paste, replace values and make it run before heading to next step.

Backup task on Task Scheduler

We can leave this our script on disk and run it manually but usually we want to run it automatically. One of the most classic options is Windows Task Scheduler. If you know what is SQL Server and what is Windows Task Scheduler you probably don’t need tutorial for scheduled tasks.

There’s one important thing. Make sure you set directory for script. It’s Start in field on the following dialog.

Setting running directory for MSSQL backup script

Now it’s time to run the script as scheduled task and make sure it runs to the glory end without any errors.

Wrapping up

We automed MSSQL backups using SqlPackage and AzCopy. Our backups are running on Windows Task Scheduler as nightly batch. I know that using DOS batch is kind of retro approach and we also have Powershell but this time I decided to go with minimalistic set of tools. In the end we have working solution for cases when exporting data-tier application and uploading it to cloud is enough for us.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    You May Also Like

    Microsoft Azure

    Copy production database to staging on Azure DevOps

    Gunnar Peipman 5
    NHibernate

    How to log NHibernate SQL to ASP.NET Core loggers

    Gunnar Peipman 49
    SQL Server

    The changes you have made require the following tables to be dropped and re-created

    Gunnar Peipman 29
    SQL Server

    Simple solution for metrics targets on MSSQL

    Gunnar Peipman 0
    Microsoft Azure

    Windows Azure – Using DataSet with cloud storage

    Gunnar Peipman 0
    Entity Framework Core

    EF Core 5.0: Using ToQueryString() method to translate LINQ query to SQL

    Gunnar Peipman 20

    1,201 thoughts on “SQL Server database backup to Azure Blob Storage”

    Leave a Reply

    Your email address will not be published. Required fields are marked *