SQL Server Backup & Restore on Azure Local

This guide walks you through backing up and restoring a SQL Server database on an Azure Local (formerly Azure Stack HCI) deployment. You’ll learn the two main approachesâ€”using native SQL Server backup to a file share and using Azure Backup for SQL Serverâ€”and how to recover your data when something goes wrong. No prior Azure Local experience is required, but you should have administrative access to the cluster and the SQL Server instance.

What You’ll Need

An Azure Local cluster (version 23H2 or later) with a registered Azure subscription.
A SQL Server instance running on a VM or directly on the cluster (SQL Server 2016 or later recommended).
Administrator credentials for the SQL Server instance (sysadmin role).
Access to the Azure portal and the Azure CLI (if using Azure Backup).
A storage location for backups: either an SMB file share on the cluster or an Azure Recovery Services vault.
If using native backups, ensure the SQL Server service account has write permissions to the backup folder.

Step-by-Step

Method 1: Native SQL Server Backup to a File Share

Create a file share on the Azure Local cluster. Use the Windows Admin Center or PowerShell to create an SMB share, e.g., \\sqlcluster\backups. Ensure the share is accessible from the SQL Server VM or host.
Grant permissions. Give the SQL Server service account (e.g., NT SERVICE\MSSQLSERVER) Modify permissions on the share and NTFS folder.
Connect to SQL Server. Open SQL Server Management Studio (SSMS) and connect to your instance.
Perform a full backup. Right-click the database â†’ Tasks â†’ Back Up. Choose “Full” as the backup type, select “Disk” as the destination, and add the file path (e.g., \\sqlcluster\backups\MyDB.bak). Click OK.
Verify the backup. After completion, check the backup file exists on the share and note the timestamp.
Schedule regular backups (optional). Use SQL Server Agent to create a job that runs a full backup daily and transaction log backups every 15 minutes. Use the same share path.

Method 2: Azure Backup for SQL Server

Create a Recovery Services vault. In the Azure portal, search for “Recovery Services vaults” and create one in the same region as your Azure Local cluster.
Register the SQL Server VM. In the vault, go to “Backup” â†’ “SQL Server in Azure VM”. Follow the prompts to register the VM that hosts SQL Server. This requires the Azure Backup extension to be installed on the VM.
Configure backup policy. Choose a policy (e.g., daily full, hourly log) and select the databases you want to protect. Enable backup.
Run an on-demand backup. In the vault, under “Backup items”, select the database and click “Backup now” to take an immediate full backup.

Restoring a Database

For native backups: In SSMS, right-click Databases â†’ Restore Database. Select “Device” and browse to the .bak file. Choose the target database name (or create a new one). Under “Restore plan”, select the backups to restore (full + log). Click OK.
For Azure Backup: In the Azure portal, go to your Recovery Services vault â†’ Backup items â†’ SQL Server. Select the database and click “Restore”. Choose the restore point (time), and select the target server (the same or a different VM). Follow the prompts to complete the restore.
Test the restored database. Run a query like SELECT COUNT(*) FROM sys.tables to ensure the database is accessible and data is intact.

Common Mistakes

Wrong permissions on the backup share. If the SQL Server service account lacks write access, the backup fails with an access-denied error. Always test the share path with a simple file creation.
Backing up to a local disk on the VM. If the VM is on the cluster, a local disk may not survive a node failure. Use a cluster-shared volume or an SMB share to ensure high availability.
Forgetting transaction log backups. Without log backups, you can only restore to the last full backup, losing recent changes. For critical databases, schedule log backups frequently.
Restoring over an existing database without proper recovery state. If you don’t use WITH REPLACE (when needed) or choose the wrong recovery state (e.g., RESTORE WITH RECOVERY vs. NORECOVERY), the restore may fail or leave the database in a restoring state.
Not verifying backups. A backup file that is corrupted or incomplete is useless. Regularly test restores on a separate server.

Tips

Use a naming convention for backup files that includes the database name and date, e.g., MyDB_20250101.bak, to avoid overwriting.
Enable compression on backups to save space and speed up the process. In SSMS, under Backup Options, check “Compress backup”.
Monitor backup health using SQL Server Agent alerts or Azure Monitor for Azure Backup. Set up email notifications for failures.
Store backups off-site by copying the .bak files to Azure Blob Storage or using Azure Backup’s geo-redundant storage for disaster recovery.
Document your restore procedure and test it quarterly. Include the exact commands and steps so any team member can execute it.

Troubleshooting

Backup fails with “Operating system error 5 (Access is denied)”. Check the share and NTFS permissions. Grant the SQL Server service account Modify rights. Also verify the SQL Server service is running under a domain account that can access the share.
Azure Backup extension fails to install. Ensure the VM has internet connectivity to Azure and that the Azure Backup extension is supported on your OS version. Try installing manually via the Azure portal or CLI.
Restore fails with “The backup set holds a backup of a database other than the existing”. This means the backup is from a different database. Use WITH REPLACE in the T-SQL RESTORE command if you intend to overwrite the existing database.
Restore is stuck in “Restoring” state. This happens when you restore with NORECOVERY. To bring the database online, run RESTORE DATABASE MyDB WITH RECOVERY.
Cannot connect to the file share from the SQL Server VM. Check network security rules, firewall, and DNS resolution. Test with Test-NetConnection sqlcluster -Port 445.
Azure Backup restore to a different VM fails. Ensure the target VM is in the same region and has the same SQL Server version. Also check that the target VM has the Azure Backup extension installed.

Frequently Asked Questions

Can I use Azure Backup for SQL Server on Azure Local without a VM?

No. Azure Backup for SQL Server requires the SQL Server to be running on an Azure VM (including VMs on Azure Local). If SQL Server runs directly on the host (not in a VM), you must use native backups to a file share.

What is the difference between a full and a differential backup?

A full backup contains the entire database at a point in time. A differential backup contains only changes since the last full backup. To restore a differential, you need the last full backup plus the differential. Differential backups are smaller and faster than full backups.

How often should I take transaction log backups?

For databases with a low recovery point objective (RPO), take log backups every 10-15 minutes. For less critical databases, hourly is common. The frequency depends on how much data loss you can tolerate.

Can I restore a database to a different instance on Azure Local?

Yes, you can restore a .bak file to any SQL Server instance that is the same or newer version. In SSMS, during restore, you can change the target database name and server. For Azure Backup, you can restore to a different VM as long as it’s in the same region and meets prerequisites.

Does Azure Backup for SQL Server support Always On Availability Groups?

Yes, Azure Backup supports SQL Server Always On Availability Groups. You can protect the availability group listener, and backups are taken from a preferred replica. Ensure the extension is installed on all replicas.

How do I verify that a backup is valid without restoring it?

Use the RESTORE VERIFYONLY FROM DISK = 'C:\backup.bak' command. This checks the backup file integrity without restoring data. It does not guarantee that the data is logically correct, but it catches physical corruption.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →