Automating SQL Script Execution Across Multiple Databases

As a database administrator, our responsibilities are ot only to keep the database online but also to deploy the SQL scripts that are used to change the schema of the database. If you are working in a company that has many database servers, the task becomes crucial and challenging. Let me explain how the automation of SQL script execution becomes crucial with a simple scenario.

For instance, your organization has a development, UAT, and Production environment. You have made changes in the schema because you have introduced new features. Suppose the application changes rolled out to all web servers, but the database changes did not deploy properly on some client databases. This situation caused an outage for some clients because the schema was not updated correctly. Also, if you manually deploy the changes, you may forget to deploy the script to a random database.

Such issues can be addressed by developing custom tools that can automate the script deployment on all databases. These tools can also help streamline the process and reduce the associated risk. In our organization, we use a set of Powershell scripts that deploy all the schema changes on all databases. There are many tools available in the market that are more efficient and provide more flexibility in script deployment. One of them is dbForge Studio for SQL Server.

Benefits of Automating SQL Script Execution

There are certain benefits to automating the execution of SQL scripts.

Developers and DBAs do not need to run the script manually. When you make a small change in stored procedure to fix a bug. Now, after updating the stored procedure, you must run it across all databases. Imagine a SQL Server that contains thousands of client databases that host the same application. This process increases the chances of human error and much human effort and time is wasted. Automating the entire process reduces the chances of error and requires minimal effort.
We can also schedule the execution of the SQL scripts using automation tools. This helps to manage the downtime window and helps to keep the business running.
We can also configure a monitoring and logging mechanism, which helps to monitor the entire process, and in case any error occurs, we can easily fix them.

In this article, we will learn how to automate the SQL script execution using sp_msforeachdb stored procedure, PowerShell scripts, and dbForge Studio for SQL Server.

Method 1: Using dbForge Studio and Command Line

In this section, we will learn how to use dbForge Studio and command line script to run the scripts across multiple database servers. For the demonstration, I have created three instances of SQL Server on my laptop.

Default instance
MSSQL_DEV
MSSQL_UAT

All three instances have a database named Stackoverflow2010. We want to execute a script that creates an index on Users table. The T-SQL code to create index is following:

USE [StackOverflow2010]
GO
CREATE NONCLUSTERED INDEX [IDX_DisplayName_Users] ON [dbo].[Users]
(
    [DisplayName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
GO

I have created a T-SQL script file using the above code.
We want to run the script on multiple instances of SQL Server; hence, we will create two text files and one MS-DOS batch file. The details are below:
Server_details.txt: This file contains the list of database server / SQL Server instances, database name, and authentication details of the servers on which we want to run the create index script. The content of the files looks like the following image:

SQLScript_Location.txt: This file contains the location of the T-SQL script that you want to execute on all databases and servers. I have kept the file in C:\SQLScripts directory.
Screenshot of file:

SQLTool.bat: The file contains a set of batch commands that are used to execute the SQL Scripts across all database servers. To execute the SQL Scripts, we are using dbForge Studio tool. Here is the code of the batch file.

Set Runtool="C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com"
-- Loop to read the servers and database names
FOR /F "eol=; tokens=1,2,3,4* delims=, " %%e in (C:\SQLScripts\SystemScripts\Databases.txt) do (
-- Loop to read the SQL files
FOR /F "eol=; tokens=1,2* delims=, " %%i in (C:\SQLScripts\SystemScripts\Scritps.txt)
do
(
%Runtool% /execute /connection:"Data Source=%%e;Encrypt=False;Enlist=False;Initial Catalog=%%f;Integrated Security=False;User ID=%%g;Password=%%h;Pooling=False;Transaction Scope Local=True" /encoding:%%i /inputfile:%%j
)
)
pause

Screenshot of file:

Run the SQLTools.bat file in command prompt.
Screenshot of process:

As you can see in the above screenshot, the script has been executed. To verify that indexes has been created, connect to the SQL Server instance and run below statement:

use StackOverflow2010
go
select @@SERVICENAME[SQL Service Name],object_id[Table Name],name[Index Name],
type_desc[Index Type]
from sys.indexes where name='IDX_DisplayName_Users'

Note that, for this demonstration, I am running the query onthe registered server so we can verify the changes across all SQL Server instances.
Output

As you can see, the index has been created on both database servers.
Now, let us explore another method.

Method 2: Using dbForge Studio Interface

This method explains how to use dbForge Studio for SQL Server interface to create database objects on multiple databases. The dbForge studio interface will help to run a specific script on multiple databases. In this example, we will run a create index statement across multiple databases created on the default instance of SQL Server.

I have restored two copies of stackoverflow2010 database named Stackoverflow2010_Dev and Stackoverflow2010_UAT. I want to create another index named IDX_CreationDate_Votes on Votes table.

The script to create index is below:

USE [StackOverflow2010]
GO
CREATE NONCLUSTERED INDEX [IDX_CreationDate_Votes] ON [dbo].[Votes]
(
    [CreationDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
GO

Now, to run the script, open dbForge Studio for SQL Server and connect to the database instance. In the query editor window paste above code. Right-click on the query editor window and select “Run on Multiple targets” option.

In “Run on multiple targets” window select Stackoverflow2010_Dev and Stackoverflow2010_UAT database and click execute.

The execution process begins. Once it is completed, run the query below to verify that the index has been created on all three databases.

use StackOverflow2010
go
select DB_NAME(DB_ID())[Database Name],object_id[Table Name],name[Index Name],
type_desc[Index Type]
from sys.indexes where name='IDX_CreationDate_Votes'
Go
use StackOverflow2010_Dev
go
select DB_NAME(DB_ID())[Database Name],object_id[Table Name],name[Index Name],
type_desc[Index Type]
from sys.indexes where name='IDX_CreationDate_Votes'
Go
use StackOverflow2010_UAT
go
select DB_NAME(DB_ID())[Database Name],object_id[Table Name],name[Index Name],
type_desc[Index Type]
from sys.indexes where name='IDX_CreationDate_Votes'

Query output:

As you can see in the above screenshot, the index has been created.
Now, let us explore some custom solutions that can be used to automate the T-SQL script execution across multiple databases.

Custom Solutions

There are a few alternatives that can be used to execute scripts on multiple databases of one or multiple servers. We can use system stored procedure named sp_msforeachdb to run the specific SQL command on all user database. Let us understand that with a simple example.

Use sp_msforeachdb stored procedure

For example, we want to create index named “IX_DisplayName_users” on “Users” table of Stackoverflow2010, Stackoverflow2010_Dev, and Stackoverflow2010_UAT database.
Here is the script to create index using sp_msforeachdb.

EXEC sp_msforeachdb '
  IF ''?'' IN  (''StackOverflow2010'', ''StackOverflow2010_Dev'', ''StackOverflow2010_UAT'')
  BEGIN
    use ?;
    If exists(select name from .sys.indexes where name=''IX_Users_DisplayName'')
    drop index [IX_Users_DisplayName] on [dbo].[users]

    CREATE NONCLUSTERED INDEX [IX_Users_DisplayName] ON [dbo].[Users]
                    (
    [DisplayName] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)

      PRINT ''Executing on database: ?''
  END
';

To verify, run below query.

use StackOverflow2010
go
select DB_NAME(DB_ID())[Database Name],object_name(object_id)[Table Name],name[Index Name],
type_desc[Index Type]
from sys.indexes where name ='IX_Users_DisplayName'
Go
use StackOverflow2010_Dev
go
select DB_NAME(DB_ID())[Database Name],object_name(object_id)[Table Name],name[Index Name],
type_desc[Index Type]
from sys.indexes where name ='IX_Users_DisplayName'
Go
use StackOverflow2010_UAT
go
select DB_NAME(DB_ID())[Database Name],object_name(object_id)[Table Name],name[Index Name],
type_desc[Index Type]
from sys.indexes where name ='IX_Users_DisplayName'

Output

As you can see, the index has been added. Let us explore another method.

Use PowerShell script

We can also use invoke-sqlcmd cmdlet to run multiple scripts on multiple databases of SQL Server. In this article, I am not covering the details of invoke-sqlcmd cmdlets. You read this article for more details.
We want to create two indexes. The first index is named “IDX_CreationDate_Posts” on “Posts” table, and another one is named “IDX_LastAccessDate_Users” on “Users” table. Both indexes must be created on “StackOverflow2010”,” StackOverflow2010_Dev”, and ”StackOverflow2010_UAT” database. Here is the code that I am using to create them.

/*Create index on Posts table*/

if exists(select name from sys.indexes where name='IDX_CreationDate_Posts')
drop index [IDX_CreationDate_Posts] on[dbo].[Posts]
go
CREATE NONCLUSTERED INDEX [IDX_CreationDate_Posts] ON [dbo].[Posts]
(
    [CreationDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
GO
/*Create index on Users table*/

if exists(select name from sys.indexes where name='IDX_LastAccessDate_Users')
drop index [IDX_LastAccessDate_Users] on [StackOverflow2010].[Users]
go
CREATE NONCLUSTERED INDEX [IDX_LastAccessDate_Users] ON [dbo].[Users]
(
    [LastAccessDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
GO

The SQL script to create both indexes is copied to “C:\SQLScripts\IndexScripts\” directory.
To create indexes, I am using a PowerShell script which is following:

# Define the SQL Server instance
$serverInstance = "PERSONAL-LAPTOP"

# List of databases to run the scripts on
$databases = @("StackOverflow2010", "StackOverflow2010_Dev", "StackOverflow2010_UAT") # Replace with your database names

# Path to the folder containing SQL scripts
$sqlScriptsPath = "C:\SQLScripts\IndexScripts" # Replace with your path

# Get all SQL scripts in the folder
$sqlFiles = Get-ChildItem -Path $sqlScriptsPath -Filter "*.sql"

foreach ($database in $databases) {
    Write-Host "Executing scripts on database: $database"

    foreach ($sqlFile in $sqlFiles) {
        # Get the full path of the SQL script
        $scriptPath = $sqlFile.FullName

        Write-Host "Executing script: $scriptPath on $database"

        try {
            # Execute the SQL script
            Invoke-Sqlcmd -ServerInstance $serverInstance -Database $database -InputFile $scriptPath
            Write-Host "Successfully executed $($sqlFile.Name) on $database"
        } catch {
            Write-Host "Failed to execute $($sqlFile.Name) on $database $_"
        }
    }
}

In the script,

We have created an array named $DB that contains the list of databases on which we want to run the scripts.
An outer loop (for each $DB in $DB) that iterates through databases specified in an array. -An Inner Loop (for each $sqlscripts in $sqlscripts) which iterates through all SQL scripts that are located in C:\SQLScripts\IndexScripts directory and execute them on current databases ($DB) of the outer loop.

Now, let us execute the script. To do that, run PowerShell as administrator and navigate to the file where the script is located.
PS C:\> C:\SQLScripts\SystemScripts\SQLTools.ps1
Screenshot

To verify that indexes have been created on all three databases, run the below statement.

use StackOverflow2010
go
select DB_NAME(DB_ID())[Database Name],object_name(object_id)[Table Name],name[Index Name],
type_desc[Index Type]
from sys.indexes where name in ('IDX_CreationDate_Posts','IDX_LastAccessDate_Users')
Go
use StackOverflow2010_Dev
go
select DB_NAME(DB_ID())[Database Name],object_name(object_id)[Table Name],name[Index Name],
type_desc[Index Type]
from sys.indexes where name in ('IDX_CreationDate_Posts','IDX_LastAccessDate_Users')
Go
use StackOverflow2010_UAT
go
select DB_NAME(DB_ID())[Database Name],object_name(object_id)[Table Name],name[Index Name],
type_desc[Index Type]
from sys.indexes where name in ('IDX_CreationDate_Posts','IDX_LastAccessDate_Users')

Screenshot

Best Practices for SQL Script Execution

Here are some best practices that we can use while executing the SQL scripts

Filtering Databases

If you are using sp_msforeachdb stored procedure to run SQL command on multiple databases, add the following piece of code to filter the database.

EXEC sp_msforeachdb '
  IF ''?'' LIKE ''AdventureWorks%''
  BEGIN
    PRINT ''Executing script on AdventureWorks database: ?''
  END
';

The above code helps to run the script on specific databases.

Validating Schema Elements

If your script contains commands to create an object, make sure that object does not exist on the database. Alternatively, you can add a small piece of code that checks the existence of the objects. For e.g.

IF EXISTS(select 1 from sys.objects where name=’object_name’)
Begin
End

If your script is inserting, updating, and deleting data from the tables, make sure the columns that are used in a script exist in the table. Also, if the table contains a foreign key or other constraints, make sure the data must follow the conditions defined in the foreign keys and constraints.

Handling Errors

Always include a proper error-handling mechanism in SQL scripts. If you are using PowerShell to execute multiple scripts, you can use the debugging and error handling modules of PowerShell.

Comparison of methods

Here is a short comparison of both methods used to automate the script execution.

PowerShell scripts

PowerShell is very versatile, and using it, we can interact with databases, files, and processes.
PowerShell has various modules that can help to integrate the SQL Server, PostgreSQL, and other database servers.
The PowerShell is preferable when you want to run the scripts on multiple servers.
Advanced scripting knowledge is needed.
Can automate the tasks using Windows task scheduler

Sp_msforeachdb store procedure

It can work with native SQL Server queries and can access a few Windows commands using xp_cmdshell procedure, which can compromise the security of the database server.
It can work with SQL Server only.
It Cannot run scripts on multiple servers
The syntax is simple, so knowledge of T-SQL is sufficient.
Use SQL Server agent and Windows task scheduler.

Conclusion

In this article, we explored different options to automate the SQL Server script execution using PowerShell scripts and sp_msforeachdb system stored procedure. Here, I have also introduced the dbForge Studio for SQL Server and how it can be easy to automate and manage the script execution on one or multiple databases across the servers. I have also covered the best practices that can be followed while using PowerShell scripts to make the process efficient and secure. Moreover, I have covered the basic comparison between sp_msforeachdb and PowerShell scripting. I hope this article helps you to determine the best way to automate the script deployment process on mission-critical production databases.

Blog