In Microsoft Azure you have 3 options for backing up your SQL server that is hosted on a VM.
In this article we’ll take a look at Azure Backup for SQL VMs option which is done via Azure Backup and Site Recovery.
In my resource group called SQL, I have a virtual network called SQL-vnet with a default subnet. In that subnet I have a windows server 2019 VM called database-server that runs SQL Server 2019 Developer Edition .
I’ve restore AdventureWorks2017 database and I’m going to configure backups for this database as well as all the system databases.
Make sure the recovery model for your database is set to Full if you are planning to take log backups and take advantage of point-in-time restore.
The first step in the process is to create a Backup and Site Recovery under the resource group. Click on add button and search for backup. Backup and Site Recovery will show up as the first option.
Creating a recovery service vault is quick and simple. Just select subscription, resource group and region and give it a name.
It usually doesn’t take more than a couple of minutes for Azure to create this resource. After the vault is created, open it and click on add backup button.
Our workload is running on Azure so there is no need to change the first option. Under what do you what to backup, select SQL Server in Azure VM
The next step for Azure portal is to search and find all the VMs that are running SQL Server. This step is called Discovery, so start discovery by clicking on its button.
Select the VMs you want to backup and click on Discover DBs.
Azure will automatically create a new account in the selected VMs called AzureWLBackupPluginSvc. It will also add a login for this account as sysadmin in SQL Server. Backup and restore process will happen through this user.
After the discovery phase is done, you can configure your backup policy.
Select your database server instances and click on OK.
Under Choose backup policy select create new. Give your policy a name and configure your full, differential and log backup policies.
This is the final snapshot of the policy I have created.
When both steps are done click on enable backup
When it’s done you can see that under backup tab there are several backup jobs in progress.
And after they finish, under backup items you’ll be able to see the databases that are backed up.
Manual backups also can be triggered for each database
The history of the operations and their status can be found under backup jobs
and if you decide to restore your database it’s as simple as selecting it from backup items and click on restore button at the top.
You can then decide if you want to rewrite the existing database or restore as a new database.
And then choose one f the full or differential backups or do a point-in-time restore.
Add comment