Automation Using SQLCMD with Batch

In the January 2009 Meeting of the Utah County SQL Server Users Group I gave a presentation on using SQLCMD.EXE with batch files for automating T-SQL scripts.

Automation Using SQLCMD with batch

1. Why SQLCMD?

   1. Repetitive processes, same targets, deployment tasks

      1. Scenarios

         1. Run schema change on multiple server/db targets

         2. CREATE/ALTER stored proc on multiple server/db targets

         3. Query Permissions on multiple server/db targets

         4. Speed! Can be much faster than GUI

2. SQLCMD basics
   
   install location:
   C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE
   
   -S SERVERNAME
   -d DBname
   -i inputfile
   >> append operator
   
   
   

3. CMD Batch Overview (2K, XP, 2003, Vista, Server 2008)
   
   REM I love comments!
   :: Protect yourself at the command line with this!
   
   %1 first argument passed
   %2 second argument passed
   
   
   

   1. Arguments examples – copying files to a server
      
      

      copy-backups.cmd
      robocopy /MAXAGE:1 "%1" "%2" "*.bak"
      

      copy-winrar.cmd
      robocopy /S "%programfiles%\WinRAR" "\\%1\c$\Program Files\WinRAR" *.exe
      

      
      
      

   2. use self-documenting batch files - commented examples or output when no arguments are passed

      IF "%~1"=="" goto help
      

      
      
      
      
      

4. Putting it all together

Examples:

Demonstrate sqlcmd to multiple targets, then parse command line by copying everything before the append operator.

The batch file, ONEOFF.CMD contains the targets for the T-SQL to be executed.
ONEOFF.CMD accepts two arguments: the name of the .SQL file containing the T-SQL commands, and a log file for output.

I saved all 3 files to the same directory( C:\BIN ) to simplify the example and ran the following:

C:\BIN>ONEOFF.CMD ONEOFF.SQL ONEOFF.LOG

The log file can be very helpful in determining which servers/databases successfully parsed the commands, and which targets failed. The first part of the .SQL file contains the following 5 lines:

SET NOCOUNT ON;
GO
SELECT '-- ' + cast(@@SERVERNAME as char(16)), cast(db_name() as char(20)), getdate()
SET NOCOUNT OFF;
GO

These 5 lines above format the outputted log file with the server name, database name, and timestamp. In the example log file output below, the "server" name is my laptop(T60-LAPTOP) since I ran the demo locally. The first 3 lines show that database Chile accepted the .SQL without errors. Lines 4-7 show errors when running against database Cuba.

------------------- -------------------- -----------------------
-- T60-LAPTOP       Chile                2009-01-29 18:43:22.793
                                                        
------------------- -------------------- -----------------------
-- T60-LAPTOP       Cuba                 2009-01-29 18:50:13.543
Msg 156, Level 15, State 1, Server T60-LAPTOP, Procedure sp_BackupNow, Line 7
Incorrect syntax near the keyword 'BEGIN'.
                                                        

Links:

SQLCMD standalone installer(Microsoft SQL Server Native Client required for install, found on same page)
http://bit.ly/sqlcmd-and-sql-native-client

Using the sqlcmd Utility
http://technet.microsoft.com/en-us/library/ms180944.aspx

Command line reference
http://www.ss64.com/index.html

NotePad ++
http://notepad-plus.sourceforge.net