How to script SQL objects, format, and run SQL scripts on multiple SQL Server instances

A part of DBA’s and developer’s role is to migrate SQL Server databases between hosts or migrate from a local development environment to a live hosted environment. It requires copying all database objects such as database schema, tables, stored procedures, functions, indexes, etc. For this, different methods can be used depending on a requirement such as a database backup restore, object SQL scripts, BACPAC files, etc.

Sometimes a .bak cannot be used due to differing versions. In a situation like this, the following needs to be performed: script the database schema and data and then run that script against the destination database.

Any DBA or developer spends much of a working day staring at SQL code and very often you have been asked to review someone else’s code, or perhaps do some performance tuning on a set of stored procedures. To make things worse, reviewing someone else’s code that is not formatted by your or company’s reference can slow your progress even worse if each SQL scripts you need to reformat manually.

In this article, we will show how to script SQL Server databases, format SQL script code to compline with your or company’s formatting standard, and deploy that formatted SQL scripts on multiple SQL Servers by using tools from ApexSQL Fundamentals Toolkit for SQL Server pack.

ApexSQL Fundamentals Toolkit for SQL Server pack consists of the following tools:

SQL scripting

ApexSQL Script is a migration tool for SQL Server databases. This tool can create SQL scripts by converting SQL database objects and data into single or multiple deployment scripts. Besides this, ApexSQL Script can create .NET solutions, or executable installers, too. With ApexSQL Script you can script only particular objects from a single or multiple SQL databases at once.

Start ApexSQL Script and open a new project window. Choose a SQL Server and the type of authentication with user credential and click the Connect button. From the list, select one or more databases, and click the Load button:

ApexSQL Script the new Project window

In the Main grid, select SQL objects that want to be scripted. To script the structure of the objects without data, use the Structure view:

The Structure view

Furthermore, to include data in the scripts, use the Data view:

The Data view

In our example, only the structure of all objects in a chosen database will be scripted.

Once the SQL objects under the Structure view are selected, click the Script button:

The Script button under the Home tab

The Script wizard will be initialed on which the scripting mode and output type can be chosen.

In the first window of the Script wizard, the scripting mode needs to be chosen:

The Script wizard window

  • Structure – script structure of the selected objects (DDL scripting)
  • Data – script data of the selected objects (DML scripting)
  • Structure and data – combinate of the Structure (DDL) and Data (DML) scripting mode

As mentioned earlier, we are interested in DDL scripting only, so in this step, the Structure scripting mode will be selected. Next, on the Output type window, select the Script folder option:

Script wizard - Output type window

  • SQL script – will put all SQL objects and data into a single script file per chosen database
  • Script Folder – will create a folder with every object scripted in a separate SQL file. All objects will be arranged through subfolders by object type
  • Source Control – will create a database folder in a source control repository, all objects will be scripted as a separate SQL file and arranged through subfolders by object type
  • C# solution – the C# solution packed will be created with all scripted SQL objects
  • Executable installer – all scripted objects will be a package as an executable installer

On the Dependencies window, by default, the dependent objects will be included, if there are any. Since we script the whole database, the dependencies grid, it will probably be empty:

Script wizard - Dependencies window

On the Script folder options window, choose the location for the scripts and click the Create button:

Script wizard - Script folder options window

Once, the scripting process finishes, the results window will appear with information about which objects are scripted:

Script wizard - Results

As can be seen, under the chosen location, all objects are scripted and placed under the appropriate sub-folder:

Scripted SQL objects

SQL formatting

As it is mentioned earlier, imagine that you get a task to review all the above created scripts in order to do some performance tuning before the scripts have been executed on multiple SQL Servers.

Now, before starting reviewing and optimizing scripts, all of them need to be formatted to apply your or company’s formatting standard.

A well-formatted SQL code is easily readable and reviewing such code can be much easier and faster than a non-formatted SQL code.

In a situation like this, ApexSQL Refactor can be very handy. ApexSQL Refactor is an add-in that integrates into SSMS and Visual Studio, has over 200 SQL query formatting options for creating SQL formatting styles and features for applying a style on one or bulk T-SQL scripts at once.

Start SSMS and from ApexSQL main menu, choose the Format SQL scripts command:

Format SQL scripts command

The Format SQL scripts window will appear. Here, navigate to the folder where are all script objects located and select a sub-folder which scripts want to be formatted. From the Profile drop-down box, choose your formatting profile:

Format SQL scripts window

If the Overwrite box is not checked, when the OK button is clicked, for each script under the selected sub-folder, the new scripts will be created and formatted, while original scripts will stay intact.

A new name for every script will be created by using a template, that is set in the File name template box of the Format SQL scripts window:

Formatted scripts

To format scripts without creating a new once, check the Overwrite box:

Format SQL scripts - Overwrite command

Once all scripts are formatted you are ready to review them.

Run multiple SQL scripts on multiple SQL Server databases

Now, when the SQL scripts are formatted and reviewed, the next step is to execute some of the scripts on multiple SQL servers. For this purpose, the ApexSQL Propagate can be used.

Run ApexSQL Propagate and from the Home tab, click the New button:

New burton under the Home tab

From the New project window, under the Scripts tab, click the Create new script list button:

New project window

The Script list window will appear, here click the Add button:

Script list window add new list

In the Select script file window navigate to the scripts that want to be executed and click the Open button:

Select script file window

All scripts will be listed in the Script list window:

Script list window

Additionally, the order of scripts for execution can be set by using the Move up/down buttons:

Move up/down buttons

Once the order of the scripts is set, click the Save button. In the Save scripts list as window, set the location and the name for the newly created execution script list and click the Save button:

Save scripts list as window

The newly created list will be loaded in the Scripts tab:

Scripts tab

Now, to set on which databases these scripts will be run, go to the Databases tab and click on the New database list button. The Database list window will be shown, and from here click the Add database(s) button:

Database list window, add a database list

Under the Add database window, click the Add server(s) button:

Add database window

The Connection to SQL Server window will appear:

Connection to SQL Server window

Add as many SQL Server as needed and check the databases on which scripts need to be executed:

Checked the databases on which scripts will be executed

When the databases are chosen, click the OK button. The list of databases will be loaded to the Databases tab:

Databases tab

Once the scripts and databases are set, click the Load button. The main window will be shown, and on its left side, the Script and Database panels with created lists will be displayed:

Scripts and database panel

If you need some scripts to be modified before executing, select a script in the Scripts panel, and in the Script preview panel, the code will appear, where it can be edited:

Script preview panel

Now, click the Execute button from the Home tab to execute the scripts against selected SQL databases:

Executed the scripts

The results will be displayed in the Results window:

Results window

Now, check the database to see if the objects from the scripts are created:

SSMS Object Explorer panel


With ApexSQL Fundamentals Toolkit for SQL Server pack, you can migrate the SQL Server databases on multiple SQL servers simply and easily using features from this pack. This will save you a lot of time and keep you from the trouble that can occur during manual migration.


January 12, 2021