Using Cloud Shell To Run SQL Scripts On Multiple Azure SQL Databases

If you have many databases (maybe hundreds or even thousands), you need efficient ways to manage them. Using CloudShell (in the Azure Portal) you can easily automate maintenance tasks. In this post I will show how to run SQL scripts on multiple databases.

Azure Cloud Shell

Azure Cloud Shell is an interactive shell built into the Azure Portal. You can use it from any web browser and it supports PowerShell scripts as well as Bash. It also comes with Azure CLI pre-installed.

You access it either by going to or by clicking the Cloud Shell icon in the Azure Portal:

Azure Cloud Shell icon

The first time you start Cloud Shell, it goes through some setup. It needs a storage account and will help you create one if necessary.

Install the Azure Az PowerShell module

You are going to need the Az PowerShell module. Currently (as of March 2022) it is not preinstalled in Cloud Shell.

Start Cloud Shell and make sure you have selected PowerShell:

Azure Cloud Shell

To install the Az PowerShell module, run this script:

1Install-Module -Name Az -Scope CurrentUser -Repository PSGallery -Force

The installation should take a couple of minutes. Don't worry if it seems to be stuck for a few minutes at first.

Preparing the scripts

There is an icon for uploading/downloading files to Cloud Shell.

Azure Cloud Shell upload file

For demo purposes, I have chosen to run Ola Hallengren's maintenance scripts (download). I'm using the CommandLog.sql, CommandExecute.sql and IndexMaintenance.sql scripts.

Also I created a file with my own script CloudShellDemoScript.ps1. You can open an editor by running code.

Azure Cloud Shell edit script

This is the full script (which you can copy/paste into code):

 3A Cloud Shell (PowerShell) script to run SQL scripts on multiple Azure SQL databases.
 6This is a demo script on how to run SQL scripts on all Azure SQL databases belonging to a server.
 9Created   : 2022-03-27
10Version   : 1.0
11Author    : Johan Åhlén
12Twitter   : @how2codeinfo
13Blog      :
14Disclaimer: This script is provided "AS IS" with no warranties.
17Import-Module SQLServer
18Import-Module Az.Accounts -MinimumVersion 2.2.0
20$resourcegroup = "rg-sql"
21$servername = "sql-johanlab1"
23$databases = Get-AzSqlDatabase -ResourceGroupName $resourcegroup -ServerName $servername
24| Where-Object {$_.Edition -ne "System"}
25| Select-Object -Property ResourceId,DatabaseName,ElasticPoolName
27# Get an access token for the current user so we can login to the Azure SQL Databases
28$dbaccesstoken = (Get-AzAccessToken -ResourceUrl
30# List databases and which elastic pool they belong to
31Write-Output ($databases | Select-Object -Property DatabaseName,ElasticPoolName | Format-Table)
33foreach ($database in $databases)
35    # Display progress
36    Write-Output "Now processing database $($database.DatabaseName)"
38    # Run scripts
39    Invoke-Sqlcmd -InputFile "CommandLog.sql" -ServerInstance "$($servername)" -Database $database.DatabaseName -AccessToken $dbaccesstoken
40    Invoke-Sqlcmd -InputFile "CommandExecute.sql" -ServerInstance "$($servername)" -Database $database.DatabaseName -AccessToken $dbaccesstoken
41    Invoke-Sqlcmd -InputFile "IndexOptimize.sql" -ServerInstance "$($servername)" -Database $database.DatabaseName -AccessToken $dbaccesstoken
43    # If you wish to run a simple statement instead of a script file, this is how...
44    # Invoke-Sqlcmd -ServerInstance "$($servername)" -Database $database.DatabaseName -AccessToken $dbaccesstoken -Query "SELECT 1"

Press Ctrl+S in code to save and Ctrl+Q to quit.

Running the script

Now you should be able to see your files:

Azure Cloud Shell list files

You can execute your script by typing something like:


And you should get an output like this:

Azure Cloud Shell script results

P.S. This script can be run locally in PowerShell as well (provided you have installed the Azure Az Powershell module).

If your databases are accessible only within VNETs

If you have disabled public network access to your databases and disallowed Azure service to connect, you will have to connect Cloud Shell to the database VNET.

To change connection settings, you first need to dismount your Cloud Shell drive:

Azure Cloud Shell dismount clouddrive

Then you can setup Cloud Shell again and select your VNET.

Note that you will need a Network Profile and a Relay to connect to your VNET. See this article: Deploy Cloud Shell into an Azure virtual network.

Azure Cloud Shell connect to VNET

Good luck!

Related Posts