How to create a SQL database on multiple servers using multiple scripts

In this article, how to create a SQL database across multiple SQL Servers from multiple scripts will be described by using PowerShell, the SQLCMD utility, SQL Server Management Studio, and ApexSQL Propagate.

In the following example, three scripts represent functional areas of a sample SQL database that is going to be created on multiple SQL Server instances:

The CreateDatabase.sql script for creating a SQL database on a server instance:

/* Create database*/
USE master;
GO
CREATE DATABASE TestEnvironment
GO

The CreateTables.sql script for creating 200 tables in a database:

/* Create 200 tables*/
USE TestEnvironment
GO
DECLARE @tblNum VARCHAR(max);
DECLARE @intNum INT;
SET @intNum = 1;

WHILE @intNum <= 10
BEGIN
SET @tblNum = 'CREATE TABLE [dbo].[Test' + CAST(@intNum AS VARCHAR(10)) + '](
[col_1] INT IDENTITY(1,1) PRIMARY KEY NOT NULL, 
[col_2] [varchar](10) NOT NULL,
[col_3] [varchar](10) NOT NULL,
[col_4] [varchar](10) NOT NULL,
[col_5] [varchar](10) NOT NULL
) ON [PRIMARY]';
SET @intNum = @intNum + 1;
 EXEC (@tblNum);
END;

The InsertData.sql script for inserting 100 rows in each table:

/* Insert 100 rows into the tables */
USE TestEnvironment
GO
SET IDENTITY_INSERT [dbo].[Test1] ON
SET QUOTED_IDENTIFIER OFF
Print "Inserting data for Table: [dbo].[Test1]"
SET QUOTED_IDENTIFIER ON
INSERT INTO [dbo].[Test1]([col_1],[col_2],[col_3],[col_4],[col_5]) VALUES (1,N'HKROI',N'G',N'NCVI',N'CJTAZN')
INSERT INTO [dbo].[Test1]([col_1],[col_2],[col_3],[col_4],[col_5]) VALUES (2,N'FQVKNI',N'TMXOTA',N'FKD',N'JPQUTANZ')
…
INSERT INTO [dbo].[Test1]([col_1],[col_2],[col_3],[col_4],[col_5]) VALUES (100,N'Q',N'EPOOCXG',N'SH',N'F')
GO
SET IDENTITY_INSERT [dbo].[Test1] OFF

SET IDENTITY_INSERT [dbo].[Test2] ON
SET QUOTED_IDENTIFIER OFF
Print "Inserting data for Table: [dbo].[Test2]"
SET QUOTED_IDENTIFIER ON
INSERT INTO [dbo].[Test2]([col_1],[col_2],[col_3],[col_4],[col_5]) VALUES (1,N'HKROI',N'G',N'NCVI',N'CJTAZN')
INSERT INTO [dbo].[Test2]([col_1],[col_2],[col_3],[col_4],[col_5]) VALUES (2,N'FQVKNI',N'TMXOTA',N'FKD',N'JPQUTANZ')
…
INSERT INTO [dbo].[Test2]([col_1],[col_2],[col_3],[col_4],[col_5]) VALUES (100,N'Q',N'EPOOCXG',N'SH',N'F')
GO
SET IDENTITY_INSERT [dbo].[Test2] OFF

…

SET IDENTITY_INSERT [dbo].[Test100] ON
SET QUOTED_IDENTIFIER OFF
Print "Inserting data for Table: [dbo].[Test2]"
SET QUOTED_IDENTIFIER ON
INSERT INTO [dbo].[Test100]([col_1],[col_2],[col_3],[col_4],[col_5]) VALUES (1,N'HKROI',N'G',N'NCVI',N'CJTAZN')
INSERT INTO [dbo].[Test100]([col_1],[col_2],[col_3],[col_4],[col_5]) VALUES (2,N'FQVKNI',N'TMXOTA',N'FKD',N'JPQUTANZ')
…
INSERT INTO [dbo].[Test100]([col_1],[col_2],[col_3],[col_4],[col_5]) VALUES (100,N'Q',N'EPOOCXG',N'SH',N'F')
GO
SET IDENTITY_INSERT [dbo].[Test100] OFF

Propagate scripts on multiple servers by using PowerShell

Windows PowerShell® can be used to run multiple scripts across multiple SQL Server instances. In an example below, three scripts will be run across servers with the following statements:

Invoke-Sqlcmd -InputFile “c:\ScriptFolder\CreateDatabase.sql” -serverinstance “WIN-TTLMDSEJOGD\LENOVO” -Username SpockLog -Password test -Database master;
Invoke-Sqlcmd -InputFile “c:\ScriptFolder\CreateTables.sql” -serverinstance “WIN-TTLMDSEJOGD\LENOVO” -Username SpockLog -Password test -Database master;
Invoke-Sqlcmd -InputFile “c:\ScriptFolder\InsertData.sql” -serverinstance “WIN-TTLMDSEJOGD\LENOVO” -Username SpockLog -Password test -Database master;
Invoke-Sqlcmd -InputFile “c:\ScriptFolder\CreateDatabase.sql” -serverinstance “MCA\LENOVO” -Database master;
Invoke-Sqlcmd -InputFile “c:\ScriptFolder\CreateTables.sql” -serverinstance “MCA\LENOVO” -Database master;
Invoke-Sqlcmd -InputFile “c:\ScriptFolder\InsertData.sql” -serverinstance “MCA\LENOVO” -Database master;

The Invoke-Sqlcmd cmdlet executes the scripts:

If all scripts are contained in the same folder code can be shortened by executing all scripts in the folder:

Get-ChildItem -Path “C:\ScriptFolder” -Filter “*.sql” | % {Invoke-Sqlcmd -InputFile $_.FullName}
foreach ($f in Get-ChildItem -path “C:\ScriptFolder” -Filter *.sql)
{
Invoke-Sqlcmd -serverinstance “WIN-TTLMDSEJOGD\LENOVO” -Username SpockLog -Password test -Database master -InputFile $f.fullname
Invoke-Sqlcmd -serverinstance “MCA\LENOVO” -Database master -InputFile $f.fullname
}

The Get-ChildItem command returns all files in the “C:\ScriptFolder” directory. In the case when a directory doesn’t contain only SQL scripts by using the -Filter *.sql command it will only retrieve files with the specified extension:

Propagate scripts on multiple servers by using SQLCMD

The SQLCMD utility is introduced in SQL Server 2005. Scripts can be run one by one by executing the commands in the Command Prompt:

Invoking SQLCMD several times can be avoided by writing a .bat file and using the :r command. The :r command in a script tells the SQLCMD utility to include the scripts referenced in the file. In this example, the file includes all three scripts to perform database creation, table creation, and data inserts.

Save the following commands as a Remote.bat file:

SELECT @@SERVERNAME, SERVERPROPERTY(‘servername’)
GO
:Connect WIN-TTLMDSEJOGD\LENOVO -U SpockLog -P test
SELECT @@SERVERNAME, SERVERPROPERTY(‘servername’)
GO
:r C:\ScriptFolder\CreateDatabase.sql
:r C:\ScriptFolder\CreateTables.sql
:r C:\ScriptFolder\InsertData.sql
PRINT ‘COMPLETED’
GO
SELECT @@SERVERNAME, SERVERPROPERTY(‘servername’)
GO
:Connect MCA\LENOVO
SELECT @@SERVERNAME, SERVERPROPERTY(‘servername’)
GO
:r C:\ScriptFolder\CreateDatabase.sql
:r C:\ScriptFolder\CreateTables.sql
:r C:\ScriptFolder\InsertData.sql
PRINT ‘COMPLETED’
GO

Execute the Remote.bat file by running the following SQLCMD command:

SQLCMD -E -d master -i C:\Remote.bat
PAUSE

The -E option means that the command uses a trusted connection, the -d option states a USE <database_name> a statement that specifies the initial database, and the -i option states the file patch that contains a .bat file.

Propagate scripts on multiple servers in SQL Server Management Studio

To propagate scripts on multiple servers by using Central Management Servers:

In the View menu, select the Registered Servers command:
Right-click Central Management Servers and select the Register Central Management Server command and register the central management server:

Note that a SQL Server instance chosen to be a central management server should be a server that is not needed for querying because a server in the central management server cannot have the same name as the any of the registered servers. Trying to register a central management server in a server group will produce the following error:
Right-click the registered central management server and then select the New Server Group command and enter a group name and description:
Right-click the central management server group and select the New Server Registration command:
In the New Server Registration dialog, register instances of SQL Server:
To execute a script against all servers in a group right-click the server directory and select the New Query command:

Paste a script in the query window and execute it:

Repeat the steps 6. and 7. for all scripts that need to be executed.

The downsides of the Central Management Servers is that multiple folders cannot be selected inside of the Central Management System, and querying against two groups would require creating a new directory or executing a script or a query group by group.

Using Central Management Servers for executing scripts on a multiple SQL Server instances can be avoided by using Local Server Groups.

To propagate scripts against multiple servers by using a local server group:

In the View menu, select the Registered Servers command:
Right-click Local Server Groups and select the New Server Group command:
Add the server group:
Right-click the created group and select the New Server Registration command:
Register, test, and save all servers to propagate scripts across:
To execute a script on all registered servers right-click the Local Server Groups and select the New Query command:

Paste a script in the query window and execute it:

Repeat the steps 6. and 7. for all scripts that need to be executed.

Both methods in SQL Server Management Studio require manual opening and pasting all scripts. The scripts can be propagated only on registered servers that also need to be added manually.

Propagate scripts on multiple servers by using ApexSQL Propagate

To propagate scripts without the additional scripting and without the need to manually open and paste all scripts, use ApexSQL Propagate, a SQL Server database deployment tool that can create SQL database or update an existing one from SQL scripts.

To create new databases on multiple servers by using ApexSQL Propagate follow these steps:

Run ApexSQL Propagate and click the New button from the Home tab to initiate the new project:
Under the Scripts tab, in the New project window, click the Create new script list button to open the Script list window:

Click the Add script(s) button in the Scripts list window and select the three scripts mentioned at the beginning of the article:

Quick tip:

If all scripts are not in the same directory, click the Add button to add scripts from the different directory

Click the Save button and let’s name the script list MultiDBCreate. Once saved, the script list will be loaded in the Scripts tab:
Click the Next button or switch to the Databases tab in the New project window to choose servers where databases should be created
Click the New database list button and the Database list window will pop-up:
To add databases, just click the Add database(s) button and the Add database window will be shown where the Add server(s) buttons should be clicked and the server name and authentication type should be provided:

Click the Add server(s) button to add all desired servers.
On each server, choose the master database, as that’s the one that will be used to create the TestEnvironment database, and click the OK button to move them in the Database list window:
Click the Save button in the Database list window and name the database list DeployDBs list:
Once saved, the newly created database list will be shown in the Databases tab:
Once both script and database lists are created, click the Load button

The main window will be shown along with the Script and Database panels that contain previously created lists, and the Script preview panel:
In order to set execution error handling, click the Options button from the Home tab and switch to the Output tab and choose the option from the Error handling section:
After setting all parameters, click the Execute button from the Home tab:

After executing scripts, the Results window will be shown and results can be reviewed:

In case of an error, the Execution info tab in the Results window will show information about it:
Once the Results window is closed, the same information can be seen in the main application window, by showing the Results panel with a click on the Results button from the View tab:

Additionally, the results view can be viewed by scripts or by databases with a click on the buttons from the View tab under the Results section.