Professional Microsoft® SQL Server® 2008 Administration

10.4. sqlcmd

PowerShell and sqlcmd are two complementary scripting environments for managing SQL Server. Each has its strengths and weaknesses, but together they provide a powerful toolkit for the script-friendly DBA. This section covers sqlcmd; PowerShell is covered in the section "Creating Change Scripts" later in this chapter.

10.4.1. Scripting Overview

Change management is all about creating a reproducible and auditable way to deploy and manage your changes. This is impossible to do properly by having a DBA execute T-SQL scripts through a Management Studio environment. Most non-Windows system administrators use shell scripts to create a repeatable change management process for their OS and database server environments. This accepted administrative practice has the broadest acceptance and the lowest risk. Scripting leverages native OS and application capabilities and works consistently regardless of the target system, at least outside the Windows environment. Just as experienced SQL DBAs have T-SQL script libraries, DBAs working with open-source and Unix-variant systems have shell script libraries that just keep getting larger as they collect more and more useful scriptlets. Now we have the same scripting capabilities on the Windows platform, only better.

In SQL 2005, we had the first major attempt to create a scripting environment that could cross the boundary between T-SQL and the outside world. sqlcmd is still a great mechanism for mixing SQL and operating system scripts ...