Microsoft – Command Shell – Run Set of SQL Commands against set of SQLServer Instances

Microsoft – Command Shell – Run Set of SQL Commands against set of SQLServer Instances

In our little demo below, the files are:

a] ListofSQLServerInstances.txt (text file that contains list of SQL Server Instances)

b] setSQLServerMemory.sql (sql file that contains Set of SQL Commands to invoke against each SQL Server Instance)

c] deploySQLScript.cmd (deployment Agent – Get each Server name from ListofSQLServerInstances.txt and invoke sqlcmd against each SQL Server Instance)

ListofSQLServerInstances.txt

DBSales
DBFinance

setSQLServerMemory.sql

  exec sp_configure 'show advanced options', 1
   reconfigure with override;

   -- set max memory to 16 GB 
   exec sp_configure 'max server memory (MB)', 16000
   go

   reconfigure with override;

   exec sp_configure 'show advanced options', 0
   reconfigure with override;</pre>

deploySQLScript.cmd


   echo off
   for /f %%s in (type listofSQLServerInstances.txt) do (

        echo Server-name %%s
        sqlcmd -S %%s -E -i setSQLServerMemory.sql
   )

References:

  1. Dos – For Command
    http://www.computing.net/answers/programming/dos-for-command/14596.html 
  2. How to write a DOS Batch file to loop through files http://www.jamesewelch.com/2008/05/01/how-to-write-a-dos-batch-file-to-loop-through-files/

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s