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:

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. Screenshot that shows an example of Try It for Azure Cloud Shell.
Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser. Button to launch Azure Cloud Shell.
Select the Cloud Shell button on the menu bar at the upper right in the Azure portal. Screenshot that shows the Cloud Shell button in the Azure portal

To use Azure Cloud Shell:

  1. Start Cloud Shell.

  2. Select the Copy button on a code block (or command block) to copy the code or command.

  3. 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.

  4. 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.

Next steps