How to execute SQL scripts against multiple SQL databases at the same time

ApexSQL Propagate is a SQL database deployment tool, that can create SQL scripts directly in the application and set order of SQL scripts for execution. SQL scripts can be parsed prior to execution and execution results can be reviewed by databases or by scripts.

It can also execute multiple SQL scripts against multiple SQL databases in a few simple steps:

  1. Run ApexSQL Propagate and click the New button from the Home tab:
  2. This will open the New project window in which Script list can be loaded or created. To create a new script list and load SQL scripts click the Create new script list button:
  3. To add SQL scripts, click the Add scripts(s) button in the Script list window:

  4. Once the list is complete, click the Save button and name the list as desired and additionally change the order of script execution using the Move up/down buttons or commands from the right-click menu:

  5. Quick tip icon

    Quick tip:

    The organized SQL script list can be saved and re-used if there is a need for repeatable sessions

  6. Once the list is saved it will be automatically loaded in the Scripts tab:

  7. Switch to the Databases tab or click the Next button from the bottom-right corner of the New project window
  8. Let’s create now a database list with a click on the New database list button and the Database list window will be shown:

  9. To add databases to the deployment list, click the Add database(s) button and the Add database window will be shown. Click the Add server(s) button and the Connect to SQL Server window will pop-up:
  10. Add as many servers as needed to connect to all databases on which scripts need to be executed. Check all desired databases and once done click the OK button to load them in the Database list window:
  11. Quick tip icon

    Quick tip:

    Database deployment lists can be saved under custom names allowing the creation of multiple deployment scenarios or even grouping SQL databases under different lists according to specific deployment needs

  12. Click the Save button in the Database list window and provide the database list name:

  13. After the database list is saved, it will be automatically loaded in the Databases tab:

  14. Once the databases and scripts are set, click the Load button
  15. After everything is loaded, the main window will be shown along with the Script and Database panels that contain previously created lists, and the Script preview panel in which any SQL script from the list can be reviewed and edited before the execution:

  16. Another useful option is controlling a deployment process in case of encountered errors. It is possible to predefine courses of action in a case that an error appears during deployment. It is a simple but handy option that helps control the process. This option is located under the Output tab in the Options window that can be accessed from the Home tab of the main application window and offers several  Error handling options:
  17. The next step is to simply run the process using the Execute button from the Home tab:

  18. SQL scripts will be executed in a predefined order against selected SQL databases. The results (or errors, if any) will be displayed in the Results window:

  19. At the end, once the Results window is closed, the same execution information can be reviewed from the main application window under the Results panel, which can be shown on a click on the Results button from the View tab:

    Also, view of the execution results can be changed with a click on the View by scripts or View by databases buttons from the View tab:

The main advantage of using this feature is reflected in its simplicity and multiple functionalities: it can save time while allowing complete control over the execution of multiple scripts against multiple SQL databases.

March 12, 2015