Use CLI to move an Azure SQL database in a SQL elastic pool

This Azure CLI script example creates two elastic pools and moves an Azure SQL database from one SQL elastic pool into another SQL elastic pool, and then moves the database out of elastic pool to a single database compute size.

If you don't have an Azure subscription, create a free account before you begin.

Use 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 block. Selecting Try It doesn't automatically copy the code to Cloud Shell. 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. Launch Cloud Shell in a new window
Select the Cloud Shell button on the top-right menu bar in the Azure portal. Cloud Shell button in the Azure portal

To run the code in this article in Azure Cloud Shell:

  1. Start Cloud Shell.

  2. Select the Copy button on a code block to copy the code.

  3. Paste the code 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.

If you choose to install and use the CLI locally, this topic requires that you are running the Azure CLI version 2.0 or later. Run az --version to find the version. If you need to install or upgrade, see Install the Azure CLI.

Sample script

#!/bin/bash

# set execution context (if necessary)
az account set --subscription <replace with your subscription name or id>

# Set the resource group name and location for your server
resourceGroupName=myResourceGroup$RANDOM
location=westus2

# Set an admin login and password for your database
adminlogin=ServerAdmin
password=`openssl rand -base64 16`
# password=<EnterYourComplexPasswordHere1>

# The logical server name has to be unique in the system
servername=server$RANDOM

# Create a resource group
az group create \
    --name $resourceGroupName \
    --location $location

# Create a logical server in the resource group
az sql server create \
    --name $servername \
    --resource-group $resourceGroupName \
    --location $location \
    --admin-user $adminlogin \
    --admin-password $password

# Create two pools in the logical server
az sql elastic-pool create \
    --resource-group $resourceGroupName \
    --server $servername \
    --name myFirstPool \
    --edition GeneralPurpose \
    --family Gen4 \
    --capacity 1
az sql elastic-pool create \
    --resource-group $resourceGroupName \
    --server $servername \
    --name mySecondPool \
    --edition GeneralPurpose \
    --family Gen4 \
    --capacity 1

# Create a database in the first pool
az sql db create \
    --resource-group $resourceGroupName \
    --server $servername \
    --name mySampleDatabase \
    --elastic-pool myFirstPool

# Move the database to the second pool - create command updates the db if it exists
az sql db create \
    --resource-group $resourceGroupName \
    --server $servername \
    --name mySampleDatabase \
    --elastic-pool mySecondPool

# Move the database to standalone S0 service tier
az sql db create \
    --resource-group $resourceGroupName \
    --server $servername \
    --name mySampleDatabase \
    --service-objective S0

# Echo random password
echo $password

Clean up deployment

Use the following command to remove the resource group and all resources associated with it.

az group delete --name $resourceGroupName

Script explanation

This script uses the following commands. Each command in the table links to command specific documentation.

Command Notes
az group create Creates a resource group in which all resources are stored.
az sql server create Creates a SQL Database server that hosts single databases and elastic pools.
az sql elastic-pools create Creates an elastic pool.
az sql db create Creates a single database or a database in an elastic pool.
az sql db update Updates database properties or moves a database into, out of, or between elastic pools.
az group delete Deletes a resource group including all nested resources.

Next steps

For more information on the Azure CLI, see Azure CLI documentation.

Additional SQL Database CLI script samples can be found in the Azure SQL Database documentation.