How to Query a SQL Database with SQLCMD in Batch Script

Automating database tasks, like creating a backup, generating a daily report, or dropping stale tables, requires sending structured queries directly from your script to a Microsoft SQL Server (MSSQL). While you can't run native SQL inside a .bat file without a helper, Microsoft provides the sqlcmd utility precisely for this purpose.

In this guide, we will demonstrate how to run individual SQL queries or pass an entire .sql script file to your database.

Prerequisites

SQLCMD Availability

The sqlcmd utility is not included with Windows by default. It is installed alongside SQL Server, SQL Server Management Studio (SSMS), or as a standalone download from Microsoft's SQLCMD utility page. Verify it is installed and available in your system PATH before running these scripts:

sqlcmd -?

If this command returns an error, sqlcmd is not installed or not in your PATH.

Method 1: Running an Inline SQL Query

If you only need to run a short command (e.g., pulling a specific row count or triggering a stored procedure), you can pass the query directly using the -Q flag.

Implementation Script

@echo off
setlocal enabledelayedexpansion

:: Define connection parameters
set "sqlServer=LOCALHOST\SQLEXPRESS"
set "dbName=MyDatabase"

:: Define the inline query
set "query=SELECT COUNT(*) AS UserCount FROM Users;"

echo Running query on %sqlServer%...
echo.

:: Execute query via sqlcmd (using Windows Authentication -E)
sqlcmd -S "%sqlServer%" -d "%dbName%" -E -Q "%query%" -W -h -1

if !errorlevel! NEQ 0 (
    echo.
    echo [ERROR] sqlcmd failed with exit code: !errorlevel!
    pause
    exit /b !errorlevel!
)

echo.
pause

SQLCMD Flag Reference

Flag	Description
-S	Server/Instance name
-d	Database name
-E	Use Trusted Connection (Windows Authentication)
-Q	Execute the query and exit immediately
-W	Remove trailing spaces from output columns
-h -1	Remove column headers, returning just the raw data

Capturing the Result into a Variable

To use the query result in subsequent Batch logic, wrap the sqlcmd call in a for /f loop:

@echo off
setlocal enabledelayedexpansion

set "sqlServer=LOCALHOST\SQLEXPRESS"
set "dbName=MyDatabase"

:: Capture the query result into a variable
for /f "usebackq delims=" %%A in (`
    sqlcmd -S "%sqlServer%" -d "%dbName%" -E -Q "SET NOCOUNT ON; SELECT COUNT(*) FROM Users;" -W -h -1
`) do set "userCount=%%A"

if not defined userCount (
    echo [ERROR] Failed to retrieve user count.
    pause
    exit /b 1
)

echo Total users: !userCount!

:: Use the result in conditional logic
if !userCount! GTR 1000 (
    echo [WARNING] User table has more than 1000 records.
)

pause

SET NOCOUNT ON

When capturing sqlcmd output into a variable, always include SET NOCOUNT ON; at the beginning of your query. Without it, SQL Server appends a row-count message (e.g., (1 rows affected)) to the output, which the for /f loop would capture as an additional line and could overwrite your result variable.

Method 2: Executing a .SQL Script File

For long, complex statements (like daily maintenance jobs or extensive staging setups), writing the query inline is unmanageable. Instead, save your query to a .sql file and execute it using the -i flag.

Setting up the .sql File (e.g., backup.sql)

-- backup.sql
BACKUP DATABASE MyDatabase 
TO DISK = 'C:\SQLBackups\MyDatabase_Daily.bak' 
WITH FORMAT, INIT, NAME = 'Full Backup';
GO

The Batch Script

@echo off
setlocal enabledelayedexpansion

set "sqlServer=LOCALHOST\SQLEXPRESS"
set "dbName=master"
set "sqlFile=C:\Scripts\backup.sql"
set "logFile=C:\Scripts\backup_log.txt"

:: Validate that the SQL file exists
if not exist "%sqlFile%" (
    echo [ERROR] SQL file not found: %sqlFile%
    pause
    exit /b 1
)

echo Executing SQL Script: %sqlFile%...

:: Execute the script and output results to a log file
sqlcmd -S "%sqlServer%" -d "%dbName%" -E -i "%sqlFile%" -o "%logFile%" -b

if !errorlevel! NEQ 0 (
    echo [ERROR] SQL script failed. Check %logFile% for details.
    pause
    exit /b !errorlevel!
)

echo Execution finished successfully. Log: %logFile%
pause

Additional Flags for Script Execution

Flag	Description
-i	Input file path containing the SQL statements
-o	Output file path for results, messages, and errors
-b	Abort batch on error and return a non-zero exit code

The -b flag is critical for error detection. Without it, sqlcmd may return exit code 0 even when SQL statements within the script fail, making %errorlevel% checks unreliable.

Why Query SQL from Batch?

1. Automated Backups: Running regular full and differential SQL backups using pure command-line tools without relying on the SQL Server Agent (useful for SQL Express editions).
2. ETL Jobs (Extract, Transform, Load): Downloading a CSV file via curl.exe, loading it into a staging table using sqlcmd, and firing a stored procedure to merge it into production.
3. Nightly Maintenance: Rebuilding indexes and updating database statistics during off-hours natively from a centralized Windows Task Scheduler.

Important Considerations

Authentication Security

Storing -U and -P credentials in plain text inside a Batch file is a major security risk. Whenever possible, run your script under a service account with appropriate database permissions and use -E for Windows Authentication. If SQL Authentication is required, consider reading the password from a secured file or environment variable rather than embedding it in the script:

set /p "sqlPass=" < "%USERPROFILE%\.sql_credentials"
sqlcmd -S "%sqlServer%" -d "%dbName%" -U "%sqlUser%" -P "%sqlPass%" -Q "%query%"
set "sqlPass="

Note the final set "sqlPass=" which clears the password from the environment immediately after use.

SQL Server Authentication Modes

Flag	Mode	When to Use
-E	Windows Authentication	Script runs under a domain/service account with DB access
-U user -P pass	SQL Authentication	No domain trust or service account available
-G	Azure Active Directory	Connecting to Azure SQL Database

Conclusion

Querying an MSSQL database seamlessly bridges the gap between static Windows command-line automation and dynamic data management. By mastering the sqlcmd utility, whether running quick inline queries or comprehensive external scripts, you can trigger stored procedures, run backups, and execute administrative jobs immediately and reliably.