It's a good idea to discuss scripting variables at this point before discussing different SQLCMD commands, primarily because certain commands reveal their true power only via leveraging scripting variables.
SQLCMD variables are different from T-SQL variables in many aspects. The easiest way of thinking about SQLCMD variables is that they are similar to macros that you can define in many text editors and development environments. Wherever such a variable is referenced, the query editor substitutes it with the text associated with the variable, which makes using them much more convenient then using just T-SQL variables.
Another major benefit of such variables is that they are carried forward between multiple batches. If you define a SQLCMD variable on the first batch, you can use it in all subsequent batches (unless it was undefined). Moreover, as you'll see in the next sections, these commands can be used to parameterize queries stored in different files.
Scripting variables have the following format: $(<variable name>). The variable name is not case sensitive. Scripting variables can be defined in one of the following ways:
If an environment variable name conflicts with a -v or :setvar definition or command line option, the command line option takes precedence. If a variable is referenced in a script whose value is not defined, an error message is returned and the SQLCMD session terminates. Let's consider a very simple but illustrative example of scripting variable usage.
Suppose you want to create a generic script that backs up a database to a specific location on the disk. The following line is going to be placed in the backup.sql script file:
BACKUP DATABASE $(db) TO DISK = "$(path)\$(db).bak"
Now to use it effectively you can pass in values of two variables on the SQLCMD command line as follows:
C:>SQLCMD ic:\backup.sql -vdb="pubs" path="c:\data"
You can see that to back up a different database or back up to a different location, you can execute this script again and again by modifying scripting variables and not touching the script itself.