SQL Backup using Azure File Share
Published Aug 10 2022 12:40 PM 9,729 Views

SQL Server limits the maximum backup size supported using a page blob to 1 TB. The maximum backup size supported using block blobs is limited to approximately 200 GB (50,000 blocks * 4 MB MAXTRANSFERSIZE).

In order to get over the above limitation, we can opt for Azure File Share.

SQL Backup is more than 12TiB, you can choose to backup using Azure File Share


Standard file shares can span up to 100 TiB, however this feature is not enabled by default. If you need a file share that is larger than 5 TiB, you will need to enable the large file share feature for your storage account.


Premium file shares can span up to 100 TiB without any special setting, however premium file shares are provisioned, rather than pay as you go like standard file shares. This means that provisioning file share much larger than what you need will increase the total cost of storage.


In local and zone redundant storage accounts, Azure file shares can span up to 100 TiB, however in geo- and geo-zone redundant storage accounts, Azure file shares can span only up to 5 TiB.



1. Storage Account with Premium FileShare



Enable Large File Share




2. Register for the SMB Multichannel preview with the following commands.


# Setting your active subscription to the one you want to register for the preview.

# Replace the <subscription-id> placeholder with your subscription id.

$context = Get-AzSubscription -SubscriptionId <your-subscription-id>

Set-AzContext $context


Register-AzProviderFeature -FeatureName AllowSMBMultichannel -ProviderNamespace Microsoft.Storage

Register-AzResourceProvider -ProviderNamespace Microsoft.Storage


You can also verify if the feature registration is complete

Get-AzProviderFeature -FeatureName AllowSMBMultichannel -ProviderNamespace       Microsoft.Storage




3. Enable SMB Multichannel

    Once you have created a File Storage account, you can follow the instructions to update SMB Multichannel settings for your storage          account




Note: If the SMB Multichannel option is not visible under File share settings or you get a failed to update setting error while updating the configuration, please make sure that your subscription is registered, and your account is in one of the supported regions with supported account type and replication.


4. Create a file share




You can set max capacity up to 100TB



5. Connect to FileShare from Window either using Active Directory or Storage Account

Connecting to a share using the storage account key is only appropriate for admin access. But mounting the Azure file share with the Active Directory identity of the user is preferred.



$connectTestResult = Test-NetConnection -ComputerName testsmbfileshare.file.core.windows.net -Port 445

if ($connectTestResult.TcpTestSucceeded) {

    # Save the password so the drive will persist on reboot

    cmd.exe /C "cmdkey /add:`"testsmbfileshare.file.core.windows.net`" /user:`"localhost\testsmbfileshare`" /pass:`"SxbRsNuwc1*******/8lk1TyUkqC+2+************==`""

    # Mount the drive

    New-PSDrive -Name Z -PSProvider FileSystem -Root "\\testsmbfileshare.file.core.windows.net\sqlbackup" -Persist

} else {

    Write-Error -Message "Unable to reach the Azure storage account via port 445. Check to make sure your organization or ISP is not blocking port 445, or use Azure P2S VPN, Azure S2S VPN, or Express Route to tunnel SMB traffic over a different port."



Copy this script and run this in PowerShell to map this as a network drive locally.

This script will check to see if this storage account is accessible via TCP port 445, which is the port SMB uses. If port 445 is available, your Azure file share will be persistently mounted.


Note: The script will only work on Windows Server 2012 and above



Once we had the above script is executed, we could see the Z drive as network Drive in My computer / This PC



6. On the SQL Server you need to first enable XP_cmdshell so we can configure backups to this file share.

        Enable the Advance SQL Configuration

        EXECUTE sp_configure 'show advanced options', 1; 


        -- To update the currently configured value for advanced options. 



        -- To enable the feature. 

        EXECUTE sp_configure 'xp_cmdshell', 1; 


        -- To update the currently configured value for this feature. 




We must mount the Z Drive in SQL server and opt for it to be available for backups. Therefore, we map it using the script below


xp_cmdshell 'net use Z: \\testsmbfileshare.file.core.windows.net\sqlbackup /u:localhost\testsmbfileshare SxbRsNuwc1*******/8lk1TyUkqC+2+************==`'


Get the storage account, username and access key from Step 5






7. Backup the database now to the file share subdirectory using the below command

    BACKUP DATABASE [AdventureWorks2019] TO DISK = 'Z:\AdventureWorks2019.bak' with stats = 5





Reference (optional)

SMB file shares in Azure Files | Microsoft Docs

Create an Azure file share - Azure Files | Microsoft Docs







Version history
Last update:
‎Aug 15 2022 07:15 AM
Updated by: