SQLCMD is a command line application that allows SQL queries to be written and executed from the command prompt. Sounds interesting?
Suppose you want to write a SQL script to create a database and you want it to be done something like parameterized fashion where you can simply pass your database name, data path (mdf and ldf). For this kind of scenarios SQLCMD is handy.
Follow the given SQL Query below.
CREATE DATABASE [$(DatabaseName)]
ON
(
NAME = [$(DatabaseName)],
FILENAME = '$(FilePathData)\$(DatabaseName)_Data.mdf',
SIZE = 51200 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10240 KB
)
LOG ON
(
NAME = [$(DatabaseName)_log],
FILENAME = '$(FilePathData)\$(DatabaseName)_Log.ldf',
SIZE = 102400 KB,
MAXSIZE = 2097152 MB,
FILEGROWTH = 1024 KB
)
Note : Save the SQL script as “DatabaseCreateScript.sql”.
If you can’t see the SQLCMD,
Go to Customizeà Query then select SQLCMD Mode and drag it into the Query.
Now suppose you want to execute your sql script (“DatabaseCreateScript.sql”) and you want to pass above parameters manually using command prompt, following is the simple and easy way to do that;
sqlcmd -i "DatabaseCreateScript.sql" -v DatabaseName=Vzette -S DatabaseServername -v FilePathData="C:\Vzette" -U SQLServerUsername -P SQLServerPassword
Finally refresh the databases under SQL Server Management Studio and you will see the newly created database called “Vzette” and check the path (C:\Vzette) for data files. Easy hah?
