How to Save Time and Reduce Errors by Automating Database Script Execution Across Multiple Databases

As businesses grow, so do their databases. With the increasing amount of data that needs to be processed, it can become overwhelming to manage multiple databases and execute scripts across all of them. This can lead to errors and inefficiencies, wasting valuable time and resources.

Fortunately, automation can provide a solution to these problems. By automating the execution of database scripts across multiple targets, businesses can save time, reduce errors, and increase productivity. This guide will explore the benefits of automating database script execution and provide practical tips on how to implement automation in your organization. By the end of this article, you’ll have a clear understanding of how automation can streamline your database management and improve your business operations.

Contents

• Method 1: Execute multiple scripts against multiple databases using dbForge Studio and the command line
  • Prepare the environment
  • Create the .bat file
  • Execute the .bat file
• Method 2: Execute a script against multiple databases from the dbForge Studio’s interface
• Conclusion

Method 1: Execute multiple scripts against multiple databases using dbForge Studio and the command line

Step 1: Prepare the environment

This step involves creating two text files. The first one will contain a list of connections and databases necessary for executing the scripts, while the second one will store the list of SQL scripts that need to be executed against those databases.

Create a databases .txt file

1.1 Launch a third-party text editor such as Notepad.

1.2 Enter the names of the source servers, databases, usernames, and passwords separated by commas. The list can include as many servers and databases as needed. Here is a template that you can use:

server_name1, database_name1, user_name1, password1
server_name2, database_name2, user_name2, password2
server_name3, database_name3, user_name3, password3...
...
server_nameN, database_nameN, user_nameN, passwordN

1.3 Save the text file with an appropriate name and file extension, such as, for example, “Target_Databases.txt”.

Create a scripts .txt file

1.4 Launch a third-party text editor such as Notepad.

1.5 Enter the paths of the SQL scripts that you wish to execute, along with their respective encodes, separated by commas. You can add as many scripts as you need to the list according to the following template.

file_encoding1, sql_file_path1
file_encoding2, sql_file_path2
...
file_encodingN, sql_file_pathN

1.6 Save the text file.

Step 2: Create a .bat file

Next, we need to create a .bat file with the script that will launch dbForge Studio for SQL Server to execute the scripts specified in the SQL_files.txt file against the databases specified in the Target_Databases.txt file.

2.1 Open a text editor, such as Notepad.

2.2 Enter the script for executing multiple SQL scripts against multiple databases, adjusting it to fit your needs. The example below shows the script structure:

Set Runtool="C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com"

FOR /F "eol=; tokens=1,2,3,4* delims=, " %%e in (Target_Databases.txt) do (

FOR /F "eol=; tokens=1,2* delims=, " %%i in (SQL_files.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

In the script:

• Target_Databases.txt is the name of the file listing the target servers, databases, and credentials to connect to them.
• SQL_files.txt is the name of the file storing the list of SQL scripts to execute.

Note
The value of Set Runtool is a default installation path for dbForge Studio for SQL Server. If you have changed it, you will need to specify the correct path to the tool’s .com file.

2.3 Save the script as a .bat file.

Step 3: Execute the .bat file

Now, all you need to do is run the .bat file.

As you can see, the scripts have been successfully executed against the databases we specified. It’s amazing how little preparation was required for such a stunning result on a large scale.

Method 2: Execute the script across multiple targets from the dbForge Studio’s interface

dbForge Studio for SQL Server allows you to quickly and efficiently execute a script across multiple databases. For this, simply right-click anywhere in the SQL document and select Run On Multiple Targets.

The Run On Multiple Targets wizard will open.

Let us execute the script against the selected databases.

The script has been executed successfully, and the Users table has been created in the target databases. If you run the script against multiple databases using dbForge Studio for SQL Server, the tool will open a separate SQL document for each database. This allows you to confirm the query completion status.

Here, you can specify the database server connection and provide a path to the script file or execute a script directly from the SSMS SQL document. You can also select the databases against which you want to execute the script, hide system databases for convenience, and choose between executing the script in series or in parallel.

However, it is important to note that executing a script against multiple databases in one execute operation is only possible if the databases are located on the same database server. If the databases reside on different servers, you will need to execute the script in several steps by switching the connection in the wizard.

Conclusion

In this article, we have demonstrated two methods to execute SQL scripts against multiple databases. The first one involves creating a custom script in a .bat file that allows for running multiple scripts against multiple databases. This method provides flexibility in specifying the scripts and databases to execute, making it an ideal solution for complex scenarios. The second method involves using the dbForge Studio interface, which enables executing one script against multiple databases residing on one server. This method is useful when dealing with databases located on the same server and a single script.

Both methods have their advantages and disadvantages, and choosing the appropriate one depends on the specific requirements of the task at hand. Now you are fully equipped to efficiently execute SQL scripts against multiple databases, which can potentially save you lots of time and effort in managing and maintaining SQL Server databases.

Experience the simplicity of executing scripts against multiple databases with just a few clicks. Download dbForge Studio for SQL Server today and get a free 30-day trial period to explore all its powerful features.