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

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

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*/
DECLARE @tblNum VARCHAR(max);
DECLARE @intNum INT;
SET @intNum = 1;

WHILE @intNum <= 200
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,000 rows in each table:

/* Insert 100K rows into the tables */
USE TestEnvironment
SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @i INT
SET @i = 1
WHILE @i <= 100,000
BEGIN
  INSERT INTO dbo.Test1 VALUES (N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata')

  INSERT INTO dbo.Test2 VALUES (N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata')

  INSERT INTO dbo.Test3 VALUES (N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata')INSERT INTO dbo.Test200 VALUES (N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata',N'testdata')

SET @i = @i + 1
END
COMMIT TRANSACTION

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:

Invoke-SQLcmdScript

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 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:

Get-ChildItem

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:

CommandPromptSQLCMD

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

CommandPromptSQLCMDI

The –E option means that the command uses a trusted connection, the – d option states a USE <database_name> 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:

  1. In the View menu, select the Registered Servers command
  2. 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 central management server in a server group will produce the following error:

    RegisteredServers

  3. Right-click the registered central management server and then select the New Server Group command:

    NewServerGroup

  4. Right-click the central management server group and select the New Server Registration command:

    NewServerRegistration

  5. In the New Server Registration dialog, register instances of SQL Server
  6. To execute a script against all servers in a group right-click the server directory and select the New Query command:

    NewQuery

  7. 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 it only uses Windows Authentication, 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:

  1. In the View menu, select the Registered Servers command
  2. Right-click Local Server Groups and select the New Server Group command
  3. Add the server group
  4. Right-click the created group and select the New Server Registration command:

    NewServerRegistrationCommand

  5. Register, test, and save all servers to propagate scripts across:

    NewServerRegistrationDialog

  6. To execute a script on all registered servers right-click the Local Server Groups and select the New Query command
  7. Paste a script in the Query Window and execute it:

    QueryWindow

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 Build

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

To create new databases on multiple servers by using ApexSQL Build start ApexSQL Build and select the Execute scripts on multiple databases option:

Execute Scripts On Multiple Databases

Click the Add button and choose the Add all scripts from folder option:

Add All Scripts From Folder

If all scripts are not in the same directory, select the Add scripts option to add scripts manually.

Set the On error action and open the Edit database deployment list dialog:

Edit database deployment list dialog

The options available in the On error drop down list:

On error dropdown list

In the Edit database deployment list dialog add multiple SQL Server instances and save the list:

Adding multiple SQL Server instances

In this example, the Show system databases option is selected to execute scripts in a master database domain for creating a fresh copy of a database from script.

After setting all parameters, click the Execute button:

The Execute button

Executing scripts dialog

After running scripts, the Execution results can be reviewed in the preview pane:

ReviewTheResults

In case of an error, ApexSQL Build offers a line by line review:

Line By Line Review

Useful resources:
Database Engine PowerShell Reference
sqlcmd Utility
Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio)

May 19, 2014