This article describes how to back up and restore the SQL Server databases using Azure Backup.
Azure Backup helps you to back up SQL Server databases to Azure via an Azure account. If you don't have one, you can create a free account in just a few minutes. For more information, see Create your Azure free account.
Note
When the trim is performed within the guest OS, the tracking of incremental blocks is reset, resulting in a full backup. The trim within the guest OS releases unused blocks of the virtual disk (VHDX) and optimizes the disk size. However, this reduces the size of the VHDX and changes the SequenceNumber of the tracked incremental blocks, resulting in a full backup size. Unless the purpose is to improve the efficiency of storage on the Hyper-V host side, we recommend you to stop the trim process within the guest to avoid an increase in backup size.
Backup flow for SQL Server databaseTo back up a SQL Server database to Azure and to recover it from Azure:
Note
Before you begin, ensure you've met the prerequisites for using Azure Backup to protect workloads. Here are some of the prerequisite tasks:
To protect SQL Server databases in Azure, first create a backup policy:
On the Data Protection Manager (DPM) server, select the Protection workspace.
Select New to create a protection group.
On the start page, review the guidance about creating a protection group. Then select Next.
Select Servers.
Expand the SQL Server virtual machine where the databases that you want to back up are located. You see the data sources that can be backed up from that server. Expand All SQL Shares and then select the databases that you want to back up. In this example, we select ReportServer$MSDPM2012 and ReportServer$MSDPM2012TempDB. Then select Next.
Name the protection group and then select I want online protection.
On the Specify Short-Term Goals page, include the necessary inputs to create backup points to the disk.
In this example, Retention range is set to 5 days. The backup Synchronization frequency is set to once every 15 minutes. Express Full Backup is set to 8:00 PM.
Note
In this example, a backup point is created at 8:00 PM every day. The data that has been modified since the previous day's 8:00 PM backup point is transferred. This process is called Express Full Backup. Although the transaction logs are synchronized every 15 minutes, if we need to recover the database at 9:00 PM, then the point is created by replaying the logs from the last express full backup point, which is 8:00 PM in this example.
Select Next. DPM shows the overall storage space available. It also shows the potential disk space utilization.
By default, DPM creates one volume per data source (SQL Server database). The volume is used for the initial backup copy. In this configuration, Logical Disk Manager (LDM) limits DPM protection to 300 data sources (SQL Server databases). To work around this limitation, select Co-locate data in DPM Storage Pool. If you use this option, DPM uses a single volume for multiple data sources. This setup allows DPM to protect up to 2,000 SQL Server databases.
If you select Automatically grow the volumes, then DPM can account for the increased backup volume as the production data grows. If you don't select Automatically grow the volumes, then DPM limits the backup storage to the data sources in the protection group.
If you're an administrator, you can choose to transfer this initial backup Automatically over the network and choose the time of transfer. Or choose to Manually transfer the backup. Then select Next.
The initial backup copy requires the transfer of the entire data source (SQL Server database). The backup data moves from the production server (SQL Server computer) to the DPM server. If this backup is large, then transferring the data over the network could cause bandwidth congestion. For this reason, administrators can choose to use removable media to transfer the initial backup Manually. Or they can transfer the data Automatically over the network at a specified time.
After the initial backup finishes, backups continue incrementally on the initial backup copy. Incremental backups tend to be small and are easily transferred across the network.
Choose when to run a consistency check. Then select Next.
DPM can run a consistency check on the integrity of the backup point. It calculates the checksum of the backup file on the production server (the SQL Server computer in this example) and the backed-up data for that file in DPM. If the check finds a conflict, then the backed-up file in DPM is assumed to be corrupt. DPM fixes the backed-up data by sending the blocks that correspond to the checksum mismatch. Because the consistency check is a performance-intensive operation, administrators can choose to schedule the consistency check or run it automatically.
Select the data sources to protect in Azure. Then select Next.
If you're an administrator, you can choose backup schedules and retention policies that suit your organization's policies.
In this example, backups are taken daily at 12:00 PM and 8:00 PM.
Tip
For quick recovery, keep a few short-term recovery points on your disk. These recovery points are used for operational recovery. Azure serves as a good offsite location, providing higher SLAs and guaranteed availability.
Use DPM to schedule Azure Backups after the local disk backups finish. When you follow this practice, the latest disk backup is copied to Azure.
Choose the retention policy schedule. For more information about how the retention policy works, see Use Azure Backup to replace your tape infrastructure.
In this example:
After you choose a retention policy, select Next.
Choose how to transfer the initial backup copy to Azure.
After you choose a transfer mechanism, select Next.
On the Summary page, review the policy details. Then select Create group. You can select Close and watch the job progress in the Monitoring workspace.
A recovery point is created when the first backup occurs. Rather than waiting for the schedule to run, you can manually trigger the creation of a recovery point:
In the protection group, make sure the database status is OK.
Right-click the database and then select Create recovery point.
In the drop-down menu, select Online protection. Then select OK to start the creation of a recovery point in Azure.
You can view the job progress in the Monitoring workspace.
To recover a protected entity, such as a SQL Server database, from Azure:
Open the DPM server management console. Go to the Recovery workspace to see the servers that DPM backs up. Select the database (in this example, ReportServer$MSDPM2012). Select a Recovery time that ends with Online.
Right-click the database name and select Recover.
DPM shows the details of the recovery point. Select Next. To overwrite the database, select the recovery type Recover to original instance of SQL Server. Then select Next.
In this example, DPM allows the database to be recovered to another SQL Server instance or to a standalone network folder.
On the Specify Recovery Options page, you can select the recovery options. For example, you can choose Network bandwidth usage throttling to throttle the bandwidth that recovery uses. Then select Next.
On the Summary page, you see the current recovery configuration. Select Recover.
The recovery status shows the database being recovered. You can select Close to close the wizard and view the progress in the Monitoring workspace.
When the recovery is complete, the restored database is consistent with the application.
For more information, see Azure Backup FAQ.
Manage backup to Azure for DPM servers via PowerShell.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4