Skip to main content

How to Run Database Migrations from a Batch Script

Database migrations are version-controlled changes to a database schema, such as creating tables, adding columns, modifying indexes, or updating stored procedures. Running migrations from a Batch Script automates schema updates as part of deployment pipelines, ensuring that the database structure always matches the application code being deployed. This eliminates manual SQL execution and reduces the risk of forgotten or out-of-order schema changes.

In this guide, we will explore how to run database migrations from a Batch Script using various tools and frameworks, including Entity Framework, Flyway, raw SQL scripts, and sqlcmd.

Method 1: Entity Framework Core Migrations​

For .NET projects using EF Core:

@echo off
setlocal

set "project=MyWebApp"
set "startup_project=MyWebApp"
set "context=AppDbContext"

:: Verify dotnet-ef tool is available
dotnet ef --version >nul 2>&1
if %errorlevel% neq 0 (
    echo [ERROR] dotnet-ef tool not found.
    echo Install with: dotnet tool install --global dotnet-ef
    pause
    exit /b 1
)

echo =============================================
echo   EF CORE DATABASE MIGRATION
echo =============================================
echo.

:: List pending migrations
echo [1/2] Checking pending migrations...
dotnet ef migrations list --project "%project%" --startup-project "%startup_project%" --context "%context%" --no-build

if %errorlevel% neq 0 (
    echo [ERROR] Could not list migrations. Check project and context names.
    pause
    exit /b 1
)

:: Apply migrations
echo.
echo [2/2] Applying migrations...
dotnet ef database update --project "%project%" --startup-project "%startup_project%" --context "%context%"

if %errorlevel%==0 (
    echo.
    echo [SUCCESS] Database updated to latest migration.
) else (
    echo.
    echo [ERROR] Migration failed.
    exit /b 1
)

pause

Creating a New Migration​

@echo off
setlocal

set /p "name=Migration name: "

if not defined name (
    echo [ERROR] Migration name is required.
    pause
    exit /b 1
)

dotnet ef migrations add "%name%" --project MyWebApp --startup-project MyWebApp

if %errorlevel%==0 (
    echo [OK] Migration "%name%" created.
) else (
    echo [ERROR] Failed to create migration.
)

pause

Method 2: SQL Script-Based Migrations​

Run numbered SQL scripts in order:

@echo off
setlocal enabledelayedexpansion

set "sql_dir=database\migrations"
set "server=localhost"
set "database=MyAppDB"

:: Generate locale-safe timestamp for the log filename
for /f "tokens=2 delims==" %%T in ('wmic os get LocalDateTime /value') do set "dt=%%T"
set "logfile=migration_%dt:~0,4%%dt:~4,2%%dt:~6,2%.log"

:: Verify sqlcmd is available
where sqlcmd >nul 2>&1
if %errorlevel% neq 0 (
    echo [ERROR] sqlcmd not found. Install SQL Server command-line tools.
    pause
    exit /b 1
)

:: Verify migration directory exists
if not exist "%sql_dir%\" (
    echo [ERROR] Migration directory not found: %sql_dir%
    pause
    exit /b 1
)

echo =============================================
echo   SQL MIGRATION RUNNER
echo   Server:   %server%
echo   Database: %database%
echo   Log:      %logfile%
echo =============================================
echo.

:: Find and sort migration scripts
set "count=0"
set "success=0"
set "failed=0"

for /f "delims=" %%F in ('dir /b /on "%sql_dir%\*.sql" 2^>nul') do (
    set /a count+=1
    echo [!count!] Running %%F...

    sqlcmd -S "%server%" -d "%database%" -i "%sql_dir%\%%F" -b >> "%logfile%" 2>&1

    if !errorlevel!==0 (
        echo       [OK]
        set /a success+=1
    ) else (
        echo       [FAIL]
        set /a failed+=1
        echo       Stopping due to error. See %logfile% for details.
        goto results
    )
)

if !count!==0 (
    echo No .sql files found in %sql_dir%.
    pause
    exit /b 1
)

:results
echo.
echo =============================================
echo   RESULTS
echo   Total: !count!  OK: !success!  Failed: !failed!
echo   Log: %logfile%
echo =============================================

if !failed! gtr 0 exit /b 1
pause

Migration Script Naming Convention​

Name scripts with a sequential number prefix to ensure correct execution order:

migrations/
  001_create_users_table.sql
  002_add_email_column.sql
  003_create_orders_table.sql
  004_add_indexes.sql

Method 3: Tracked Migrations (Skip Already Applied)​

Maintain a tracking table to record which migrations have been applied:

@echo off
setlocal enabledelayedexpansion

set "sql_dir=database\migrations"
set "server=localhost"
set "database=MyAppDB"
set "tracking_table=__MigrationHistory"

:: Verify sqlcmd is available
where sqlcmd >nul 2>&1
if %errorlevel% neq 0 (
    echo [ERROR] sqlcmd not found.
    pause
    exit /b 1
)

if not exist "%sql_dir%\" (
    echo [ERROR] Migration directory not found: %sql_dir%
    pause
    exit /b 1
)

echo =============================================
echo   TRACKED MIGRATION RUNNER
echo =============================================
echo.

:: Ensure tracking table exists
sqlcmd -S "%server%" -d "%database%" -b -Q ^
    "IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = '%tracking_table%') CREATE TABLE [%tracking_table%] (MigrationName NVARCHAR(255) PRIMARY KEY, AppliedAt DATETIME DEFAULT GETDATE())" >nul 2>&1

if %errorlevel% neq 0 (
    echo [ERROR] Could not connect to database or create tracking table.
    pause
    exit /b 1
)

:: Run pending migrations
set "applied=0"
set "skipped=0"
set "total=0"

for /f "delims=" %%F in ('dir /b /on "%sql_dir%\*.sql" 2^>nul') do (
    set /a total+=1

    :: Check if already applied
    set "exists=0"
    for /f %%R in ('sqlcmd -S "%server%" -d "%database%" -Q "SET NOCOUNT ON; SELECT COUNT(*) FROM [%tracking_table%] WHERE MigrationName = '%%~nF'" -h -1 -W 2^>nul') do set "exists=%%R"

    if "!exists!"=="0" (
        echo Applying: %%F
        sqlcmd -S "%server%" -d "%database%" -i "%sql_dir%\%%F" -b >nul 2>&1

        if !errorlevel!==0 (
            :: Record in tracking table
            sqlcmd -S "%server%" -d "%database%" -Q "INSERT INTO [%tracking_table%] (MigrationName) VALUES ('%%~nF')" -b >nul 2>&1
            echo       [OK]
            set /a applied+=1
        ) else (
            echo       [FAIL] Aborting.
            exit /b 1
        )
    ) else (
        echo Skipping: %%F (already applied^)
        set /a skipped+=1
    )
)

if !total!==0 (
    echo No .sql files found in %sql_dir%.
    pause
    exit /b 1
)

echo.
echo Applied: !applied!  Skipped: !skipped!
pause

Method 4: Flyway Migrations​

Flyway is a popular database migration tool that supports versioned migrations:

@echo off
setlocal

set "FLYWAY_HOME=C:\Tools\flyway"
set "url=jdbc:sqlserver://localhost;databaseName=MyAppDB;encrypt=false"
set "user=sa"

:: Verify Flyway is available
if not exist "%FLYWAY_HOME%\flyway.cmd" (
    if not exist "%FLYWAY_HOME%\flyway" (
        echo [ERROR] Flyway not found at: %FLYWAY_HOME%
        echo Download from: https://flywaydb.org/download
        pause
        exit /b 1
    )
)

echo =============================================
echo   FLYWAY MIGRATION
echo =============================================
echo.

:: Show current status
echo Migration status:
call "%FLYWAY_HOME%\flyway" -url="%url%" -user="%user%" info

if %errorlevel% neq 0 (
    echo.
    echo [ERROR] Could not connect to database. Check URL and credentials.
    pause
    exit /b 1
)

echo.

:: Apply pending migrations
echo Applying pending migrations...
call "%FLYWAY_HOME%\flyway" -url="%url%" -user="%user%" migrate

if %errorlevel%==0 (
    echo.
    echo [SUCCESS] Migrations applied.
    echo.
    call "%FLYWAY_HOME%\flyway" -url="%url%" -user="%user%" info
) else (
    echo.
    echo [ERROR] Migration failed.
    exit /b 1
)

pause
info

Flyway expects migration scripts in a sql directory with the naming convention V{version}__{description}.sql (double underscore), e.g., V1__create_users.sql, V2__add_orders.sql. Flyway tracks applied migrations automatically and only runs new ones.

Method 5: Migration with Backup and Rollback​

A production-safe migration workflow:

@echo off
setlocal enabledelayedexpansion

set "server=localhost"
set "database=MyAppDB"
set "sql_dir=database\migrations"
set "backup_dir=database\backups"

:: Verify sqlcmd is available
where sqlcmd >nul 2>&1
if %errorlevel% neq 0 (
    echo [ERROR] sqlcmd not found.
    pause
    exit /b 1
)

if not exist "%sql_dir%\" (
    echo [ERROR] Migration directory not found: %sql_dir%
    pause
    exit /b 1
)

echo =============================================
echo   PRODUCTION MIGRATION
echo =============================================
echo.

:: Step 1: Pre-migration backup
echo [1/3] Creating database backup...
if not exist "%backup_dir%" mkdir "%backup_dir%"

for /f "tokens=2 delims==" %%T in ('wmic os get LocalDateTime /value') do set "dt=%%T"
set "timestamp=%dt:~0,4%%dt:~4,2%%dt:~6,2%_%dt:~8,2%%dt:~10,2%"
set "backup_file=%backup_dir%\%database%_%timestamp%.bak"

sqlcmd -S "%server%" -b -Q ^
    "BACKUP DATABASE [%database%] TO DISK = N'%backup_file%' WITH FORMAT, INIT, COMPRESSION"

if !errorlevel! neq 0 (
    echo [ABORT] Backup failed. Cannot proceed without a backup.
    exit /b 1
)
echo       Backup: %backup_file%

:: Step 2: Apply migrations
echo [2/3] Running migrations...
set "migration_failed=false"
set "failed_script="

for /f "delims=" %%F in ('dir /b /on "%sql_dir%\*.sql" 2^>nul') do (
    echo       %%F
    sqlcmd -S "%server%" -d "%database%" -i "%sql_dir%\%%F" -b >nul 2>&1
    if !errorlevel! neq 0 (
        echo       [FAIL] Migration failed at %%F
        set "migration_failed=true"
        set "failed_script=%%F"
        goto rollback_check
    )
)

:rollback_check
if "!migration_failed!"=="true" (
    echo.
    echo [ERROR] Migration failed at: !failed_script!
    set /p "rollback=Restore database from backup? (YES/NO): "
    if /i "!rollback!"=="YES" (
        echo Restoring from backup...
        sqlcmd -S "%server%" -b -Q ^
            "ALTER DATABASE [%database%] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; RESTORE DATABASE [%database%] FROM DISK = N'%backup_file%' WITH REPLACE; ALTER DATABASE [%database%] SET MULTI_USER"

        if !errorlevel!==0 (
            echo [ROLLBACK] Database restored from backup.
        ) else (
            echo [CRITICAL] Restore failed. Manual intervention required.
            echo Backup file: %backup_file%
        )
    ) else (
        echo [WARNING] Database may be in an inconsistent state.
    )
    exit /b 1
)

:: Step 3: Verify
echo [3/3] Verifying...
for /f %%C in ('sqlcmd -S "%server%" -d "%database%" -Q "SET NOCOUNT ON; SELECT COUNT(*) FROM sys.tables" -h -1 -W 2^>nul') do set "table_count=%%C"
echo       Tables in database: !table_count!

echo.
echo [SUCCESS] All migrations applied.
pause

Common Mistakes​

The Wrong Way: Running Migrations Without Backup​

:: WRONG - No backup before modifying schema
sqlcmd -S server -d MyDB -i "drop_and_recreate.sql"
:: If it fails halfway, database is in inconsistent state

Output Concern: Schema migrations can fail midway through, leaving the database in an inconsistent state with partial changes applied. Always back up the database before running migrations, and use transactions in migration scripts where possible.

The Wrong Way: Not Tracking Applied Migrations​

:: WRONG - Re-runs all migrations every time
for %%F in (migrations\*.sql) do sqlcmd -S server -d MyDB -i "%%F"
:: Duplicate columns, duplicate tables, constraint violations

Without tracking which migrations have already been applied, re-running the script causes errors from duplicate table/column creation. Use a tracking table, Flyway, or EF Core migrations to manage state.

Best Practices​

  1. Always back up before migrating: A pre-migration backup enables instant rollback.
  2. Track applied migrations: Use a tracking table or tool (Flyway, EF Core) to prevent re-execution.
  3. Number scripts sequentially: Ensure consistent execution order across environments.
  4. Use transactions: Wrap migration scripts in transactions so failures roll back cleanly.
  5. Test migrations on staging first: Apply migrations to a staging database before production.

Conclusion​

Running database migrations from a Batch Script automates schema evolution as part of the deployment pipeline. Whether using Entity Framework Core's built-in migration system, Flyway's versioned migration framework, or custom SQL script runners with tracking tables, the pattern is the same: check for pending migrations, back up the database, apply changes in order, record what was applied, and verify the result. By integrating migrations into deployment scripts with backup and rollback capabilities, teams ensure that database changes are applied consistently, safely, and repeatably across all environments.

Table of Contents