Question: I am asking for a solution to execute custom SQL scripts on multiple `SQL Server Express` instances on schedule. 

Solutions that I have seen like `ApexSQL Propagate` and `Redgate SQL Multi Script` do not have a scheduler and I have more than 170 instances to run periodic jobs on them.

Any advice on how to optimally execute scheduled scripts will be deeply appreciated.

Answer: 
you can use powershell script and csv file, to automate most of your work

```
$csv = Import-Csv C:\sql\db.csv -Delimiter ";"

foreach ($line in $csv) {

    $DBServer = $line.DBServer
    $DBName = $line.DBName
    $CommandText = $line.CommandText

    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Data Source=$DBServer;Database=$DBName ;Integrated Security=True;"
    $sqlConnection.Open()

    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
    $sqlCommand.Connection = $sqlConnection

    $sqlCommand.CommandText = $CommandText
    $sqlCommand.ExecuteNonQuery()  
    $sqlConnection.Close()
    $sqlConnection.Dispose()       
}

```

And, sample csv file for it:

```
DBServer;DBName;CommandText
.;db;BACKUP DATABASE [db] TO  DISK = N'd:\backup\db.bak' WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
ACER;db2;BACKUP DATABASE [db2] TO  DISK = N'd:\backup\db2.bak' WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

```

This script connect to server instance DBServer, in my case to default, and to db, for backup it can be master, and execute CommandText 

It use integrated security, but you can extend script to use sql server password security, you can extend script to take sql commands from file, etc, etc, etc..

You can add all your 170 instances into csv file, and script will execute sql commands against all your servers. 

Of course, for production, script must be more complicated, with error logging.
I don't know about the scheduling side of it, or the limitations of SQL Server Express, but I do some fleet management tasks with a Windows CMD.EXE script that looks like this, with the input being a SQL script file to run on each of the servers.

```
SET SCRIPT=%1

SQLCMD -E -S "instance001_name" -d homedb001 -l 15 -i %SCRIPT% -o log001.txt -u -x -b
IF ERRORLEVEL 1 GOTO failure

SQLCMD -E -S "instance002_name" -d homedb002 -l 15 -i %SCRIPT% -o log002.txt -u -x -b
IF ERRORLEVEL 1 GOTO failure

SQLCMD -E -S "instance003_name" -d homedb003 -l 15 -i %SCRIPT% -o log003.txt -u -x -b
IF ERRORLEVEL 1 GOTO failure

GOTO terminat

:failure

ECHO Terminated due to error.

:terminat

```

The real script has more variables - to select one server to run on or "ALL", and to name the log files after today's date.

The Windows keyword to run a command at a given time is "AT", and if permissions are suitable then this can cause a Windows command to run on another of your servers. So if you have 170 windows servers... and if SQLCMD is installed on each one... (and is allowed to log in to the SQL Server, possibly with an actual password - )

Alternatively, a manual trick in Management Studio - if this will work with Express servers - is to right-click to run a query on a server group, which runs on every server in the group. I'm still learning about it, and I just tried this variation - it worked:

```
WAITFOR TIME '12:42:00';
SELECT @@SERVERNAME AS Name;

```

(I suspect that each of our servers has a slightly different time setting to decide when 12:42 actually is.)

To schedule action at 1 a.m. - make the time '00:59:00' in case daylight-saving catches you out. Lines after that are executed starting at the given time, and the result of SELECT appears as UNION ALL of the result from each server... possibly displayed in random order. And you have to leave Management Studio open overnight...