Skip to content
Tutorial Reference

How to Query a SQL Database with SQLCMD in Batch Script

Learn how to execute SQL queries in batch scripts using sqlcmd for automation tasks like backups, reports, and database maintenance in Microsoft SQL Server.

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

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

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

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

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

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.

Navigation