Using SQLCMD for Scripted Deployments
By Tom Nonmacher
In the world of SQL Server, the SQLCMD utility provides a powerful command-line tool for SQL Server instances. It is an ideal choice for managing and administering SQL Server services, especially when used for scripted deployments. SQLCMD was introduced with SQL Server 2005 and continued with newer versions, including SQL Server 2016 and SQL Server 2017. It allows ad-hoc SQL commands and scripts to be executed directly from the command prompt, or from a script file.
SQLCMD can be used to connect to any SQL Server instance, including Azure SQL and other cloud-based instances. It supports various commands that extend beyond T-SQL, providing dynamic execution capabilities. To connect to a SQL Server instance using SQLCMD, use the following command:
sqlcmd -S server_name\instance_name -U login_id -P password
In the context of scripted deployments, SQLCMD can be used to execute a script file against a SQL Server instance. This is particularly useful when you need to deploy a database across multiple servers or instances. To execute a script file, use the -i option followed by the path of the script file:
sqlcmd -S server_name\instance_name -U login_id -P password -i C:\path\to\script.sql
SQLCMD can also be used with other database systems like MySQL and DB2. For MySQL 5.7, the MySQL Shell is equivalent to SQLCMD. Though the commands are different, the functionality is similar. Here's how you can execute a script file in MySQL:
mysql -u username -p password -h host_name -D database_name < script.sql
For DB2 11.1, the CLP (Command Line Processor) is similar to SQLCMD. Just like SQLCMD, it provides a command-line interface for DB2 Server. You can execute a script file using DB2 CLP as follows:
db2 -td@ -f script.sql
In conclusion, SQLCMD is a versatile tool that can immensely simplify your SQL Server management tasks, particularly scripted deployments. It's compatible with SQL Server 2016 and 2017, Azure SQL, MySQL 5.7, and DB2 11.1. It also provides flexibility and efficiency, allowing database administrators to manage their databases conveniently and effectively.