Search code examples
sql-serversqlcmd

Trying to spin up a database from the command line if it doesn't exist

I'm trying to feed in a variable name from the command line and create a database if it does not exist. My command line is below:

sqlcmd -S localhost -i 00_SpinUp.sql -v DBName = TEST -o Script00.txt -b

where 00_SpinUp.sql is as follows:

DECLARE @DBNAME VARCHAR(MAX);
SET NOCOUNT ON
GO
IF DB_ID('$(DBNAME)') IS NULL
BEGIN
    CREATE DATABASE @DBNAME
END

Yet I'm getting a syntax error. What have I done wrong?

Solution

  • With SQLCMD variables, reference the variable in the script with $(VariableName) rather than as T-SQL variables. No declaration is needed in the script.

    SET NOCOUNT ON;
GO
IF DB_ID('$(DBNAME)') IS NULL
BEGIN
    CREATE DATABASE [$(DBNAME)];
END;