How to back up and restore an Azure Database for MySQL server using PowerShell
APPLIES TO: 
Azure Database for MySQL - Single Server
Important
Azure Database for MySQL single server is on the retirement path. We strongly recommend that you upgrade to Azure Database for MySQL flexible server. For more information about migrating to Azure Database for MySQL flexible server, see What's happening to Azure Database for MySQL Single Server?
Azure Database for MySQL servers is backed up periodically to enable restore features. Using this feature you may restore the server and all its databases to an earlier point-in-time, on a new server.
Prerequisites
To complete this how-to guide, you need:
- The Az PowerShell module installed locally or Azure Cloud Shell in the browser
- An Azure Database for MySQL server
Important
While the Az.MySql PowerShell module is in preview, you must install it separately from the Az
PowerShell module using the following command: Install-Module -Name Az.MySql -AllowPrerelease.
Once the Az.MySql PowerShell module is generally available, it becomes part of future Az
PowerShell module releases and available natively from within Azure Cloud Shell.
If you choose to use PowerShell locally, connect to your Azure account using the Connect-AzAccount cmdlet.
Azure Cloud Shell
Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. You can use either Bash or PowerShell with Cloud Shell to work with Azure services. You can use the Cloud Shell preinstalled commands to run the code in this article, without having to install anything on your local environment.
To start Azure Cloud Shell:
| Option | Example/Link |
|---|---|
| Select Try It in the upper-right corner of a code or command block. Selecting Try It doesn't automatically copy the code or command to Cloud Shell. |  |
| Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser. |  |
| Select the Cloud Shell button on the menu bar at the upper right in the Azure portal. |  |
To use Azure Cloud Shell:
Start Cloud Shell.
Select the Copy button on a code block (or command block) to copy the code or command.
Paste the code or command into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux, or by selecting Cmd+Shift+V on macOS.
Select Enter to run the code or command.
Set backup configuration
At server creation, you make the choice between configuring your server for either locally redundant or geographically redundant backups.
Note
After a server is created, the kind of redundancy it has, geographically redundant vs locally redundant, can't be changed.
While creating a server via the New-AzMySqlServer command, the GeoRedundantBackup
parameter decides your backup redundancy option. If Enabled, geo redundant backups are taken. Or
if Disabled, locally redundant backups are taken.
The backup retention period is set by the BackupRetentionDay parameter.
For more information about setting these values during server creation, see Create an Azure Database for MySQL server using PowerShell.
The backup retention period of a server can be changed as follows:
Update-AzMySqlServer -Name mydemoserver -ResourceGroupName myresourcegroup -BackupRetentionDay 10
The preceding example changes the backup retention period of mydemoserver to 10 days.
The backup retention period governs how far back a point-in-time restore can be retrieved, since it's based on available backups. Point-in-time restore is described further in the next section.
Server point-in-time restore
You can restore the server to a previous point-in-time. The restored data is copied to a new server, and the existing server is left unchanged. For example, if a table is accidentally dropped, you can restore to the time just the drop occurred. Then, you can retrieve the missing table and data from the restored copy of the server.
To restore the server, use the Restore-AzMySqlServer PowerShell cmdlet.
Run the restore command
To restore the server, run the following example from PowerShell.
$restorePointInTime = (Get-Date).AddMinutes(-10)
Get-AzMySqlServer -Name mydemoserver -ResourceGroupName myresourcegroup |
Restore-AzMySqlServer -Name mydemoserver-restored -ResourceGroupName myresourcegroup -RestorePointInTime $restorePointInTime -UsePointInTimeRestore
The PointInTimeRestore parameter set of the Restore-AzMySqlServer cmdlet requires the
following parameters:
| Setting | Suggested value | Description |
|---|---|---|
| ResourceGroupName | myresourcegroup | The resource group where the source server exists. |
| Name | mydemoserver-restored | The name of the new server that is created by the restore command. |
| RestorePointInTime | 2020-03-13T13:59:00Z | Select a point in time to restore. This date and time must be within the source server's backup retention period. Use the ISO8601 date and time format. For example, you can use your own local time zone, such as 2020-03-13T05:59:00-08:00. You can also use the UTC Zulu format, for example, 2018-03-13T13:59:00Z. |
| UsePointInTimeRestore | <SwitchParameter> |
Use point-in-time mode to restore. |
When you restore a server to an earlier point-in-time, a new server is created. The original server and its databases from the specified point-in-time are copied to the new server.
The location and pricing tier values for the restored server remain the same as the original server.
After the restore process finishes, locate the new server and verify that the data is restored as expected. The new server has the same server admin login name and password that was valid for the existing server at the time the restore was started. The password can be changed from the new server's Overview page.
The new server created during a restore does not have the VNet service endpoints that existed on the original server. These rules must be set up separately for the new server. Firewall rules from the original server are restored.
Geo restore
If you configured your server for geographically redundant backups, a new server can be created from the backup of the existing server. This new server can be created in any region that Azure Database for MySQL is available.
To create a server using a geo redundant backup, use the Restore-AzMySqlServer command with the
UseGeoRestore parameter.
Note
When a server is first created it may not be immediately available for geo restore. It may take a few hours for the necessary metadata to be populated.
To geo restore the server, run the following example from PowerShell:
Get-AzMySqlServer -Name mydemoserver -ResourceGroupName myresourcegroup |
Restore-AzMySqlServer -Name mydemoserver-georestored -ResourceGroupName myresourcegroup -Location eastus -Sku GP_Gen5_8 -UseGeoRestore
This example creates a new server called mydemoserver-georestored in the East US region that belongs to myresourcegroup. It is a General Purpose, Gen 5 server with 8 vCores. The server is created from the geo-redundant backup of mydemoserver, also in the resource group myresourcegroup.
To create the new server in a different resource group from the existing server, specify the new resource group name using the ResourceGroupName parameter as shown in the following example:
Get-AzMySqlServer -Name mydemoserver -ResourceGroupName myresourcegroup |
Restore-AzMySqlServer -Name mydemoserver-georestored -ResourceGroupName newresourcegroup -Location eastus -Sku GP_Gen5_8 -UseGeoRestore
The GeoRestore parameter set of the Restore-AzMySqlServer cmdlet requires the following
parameters:
| Setting | Suggested value | Description |
|---|---|---|
| ResourceGroupName | myresourcegroup | The name of the resource group the new server belongs to. |
| Name | mydemoserver-georestored | The name of the new server. |
| Location | eastus | The location of the new server. |
| UseGeoRestore | <SwitchParameter> |
Use geo mode to restore. |
When creating a new server using geo restore, it inherits the same storage size and pricing tier as the source server unless the Sku parameter is specified.
After the restore process finishes, locate the new server and verify that the data is restored as expected. The new server has the same server admin login name and password that was valid for the existing server at the time the restore was started. The password can be changed from the new server's Overview page.
The new server created during a restore does not have the VNet service endpoints that existed on the original server. These rules must be set up separately for this new server. Firewall rules from the original server are restored.