Creating data files in Azure Blob Storage for SQL Server database with Powershell

Storing SQL Server database files in Azure blob storage is a great solution for all the databases that are often migrated between instances, servers, virtual machines, or would have been divided between instances. This scenario also has the positive aspect to it, since the ability to create snapshot backups to Azure is seamless.

Following the steps, we will create a Azure Blob storage, where MSSQL Server database files will reside with MSSQL Server running on-prem. Assuming, that you already have the Azure account (if not, you can get a free Azure account), let’s proceed by opening the Windows Terminal in PowerShell mode.

Create resource group

First we will login using Powershell to your account, following by creating a Resource group, so all of the resources will be grouped together.

Connect-AzureRmAccount -SubscriptionId 'yyyyy-yyyyy-yyyyyy-yyyyyyyy'

After connecting, you will receive a return message on your subscription, tenantID and environment:

Once you are connected, you can proceed with creating a resource group:

New-AzureRmResourceGroup -Name DataFilesSQLServer -Location "North Europe" -Tag @{Name="DataFilesSQLServer"; Purpose="UploadingMSSQLDataFiles"; Author="Tomaz xxxx"}

You will be prompted on creation of the resource group:

You can always check your Azure Portal to check on the progress.

Create storage account

Quick powershell script to create a Azure blob storage account with a) selected and previously created resource group, b) location in north Europe c) Standard_LRS as locally redundant storage, d) cool access tier and some tags for the service.

New-AzureRmStorageAccount -ResourceGroupName 'DataFilesSQLServer' -AccountName storaccdatafilesmssql  -Location 'northeurope'  -SkuName Standard_LRS  -Kind BlobStorage  -AccessTier Cool -Tag @{Name="StorageaccountBlob"; Purpose="StoringDatabaseFiles"; Author="Tomaz xxxx"}

Receiving back the response from Azure:

Getting credentials for the blob container

Within the blob storage, we will create a blob container and for this container get the credentials as a “Shared access signature”. Create The blob container

New-AzureRmStorageContainer -ResourceGroupName 'DataFilesSQLServer' -AccountName storaccdatafilesmssql2 -ContainerName mymssqlfiles -Tag @{Name="Blob Storage container";Purpose="Storing mdf and ldf files";Author="Tomaz Kastrun"}

Checking the storage in Microsoft Azure Storage explorer, you can see in left pane, how the storage is progressing:

Getting the Shared access signature using Powershell. We need to provide an Azure Storage Context in order to receive the SAS token:

$storageAccountName = storaccdatafilesmssql2
$accountKeys = Get-AzureRmStorageAccountKey -ResourceGroupName DataFilesSQLServer -Name $storageAccountName
$storageContext = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $accountKeys[0].Value 

New-AzureStorageContainerSASToken -Name mymssqlfiles -Context $storageContext -Permission rwdl -StartTime $now.AddHours(-1) -ExpiryTime $now.AddMonths(1)

And the output will be the secret key, that will be valid for one month and it will be used for the connecting on-prem SQL Server to Azure blob storage database files.

With SAS token generated, we can now proceed to SQL Server Management studio and create the credentials, new database and store files in Azure. Mark in red are timestamps and permissions (rwdl) as described in PowerShell code.

Creating SQL Server database on on-prem SQL Server

A simple T-SQL script will create a credentials and a database with a sample table:

SECRET = 'sv=xxxxxxxxxxxxinputyoursecretxxxxxxxxxx'

ON (
NAME = 'mymssqlinazuredata',
NAME = 'mymssqlinazurelog',

USE [myMSSQLinAzure]

CREATE TABLE dbo.sampleData
,someText VARCHAR(200)
,someNmr INT

INSERT INTO dbo.sampleData (someText, someNmr)
 		  SELECT 'adding Table to Azure Storage', 10
UNION ALL SELECT 'adding files to Blob storage', 100

SELECT * FROM dbo.sampleData

You can query the database as if all the data files would have have been on-prem. By cheching the Azure Storage explorer we can see the files:

Upon checking the properties of the database, we can see that the files reside in Azure blob storage:

As always, code is available on Github.


Tagged with: , , , ,
Posted in Uncategorized
2 comments on “Creating data files in Azure Blob Storage for SQL Server database with Powershell
  1. Eitan Blumin says:

    Thanks for this!

    It would probably be useful to also have some kind of key rotation script in order to renew the SAS key in Azure and then update the credentials in SQL Server.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Follow TomazTsql on
Programs I Use: SQL Search
Programs I Use: R Studio
Programs I Use: Plan Explorer
Rdeči Noski – Charity

Rdeči noski

100% of donations made here go to charity, no deductions, no fees. For CLOWNDOCTORS - encouraging more joy and happiness to children staying in hospitals (


Top SQL Server Bloggers 2018

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Discover WordPress

A daily selection of the best content published on WordPress, collected for you by humans who love to read.


Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond - attaining enlightenment with the Microsoft Data and Cloud Platforms with a sprinkling of Open Source and supporting technologies!

SQL DBA with A Beard

He's a SQL DBA and he has a beard

Reeves Smith's SQL & BI Blog

A blog about SQL Server and the Microsoft Business Intelligence stack with some random Non-Microsoft tools thrown in for good measure.

SQL Server

for Application Developers

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond

Search Msdn

Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond


Tomaz doing BI and DEV with SQL Server and R, Python, Power BI, Azure and beyond


Bringing value to data & insights through experiences users love


R news and tutorials contributed by hundreds of R bloggers

Data Until I Die!

Data for Life :)

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Grant Fritchey

Intimidating Databases and Code

Madhivanan's SQL blog

A modern business theme

Alessandro Alpi's Blog

DevOps could be the disease you die with, but don’t die of.

Paul te Braak

Business Intelligence Blog

Sql Insane Asylum (A Blog by Pat Wright)

Information about SQL (PostgreSQL & SQL Server) from the Asylum.

Gareth's Blog

A blog about Life, SQL & Everything ...

%d bloggers like this: