Pre-Flight Checks at the Top of a Script With THROW

A deployment starts successfully in the wrong database, which is the worst kind of successful start. Pre-flight checks stop the script before it changes anything. Check the target, prerequisites, and execution mode at the top.

Pre-Flight Checks for the Server and Database

I compare the expected server and database before any mutation. @@SERVERNAME reflects configured server naming, so review it when servers have been renamed. The intended target belongs in an explicit variable. A connection dropdown alone is not an execution guard.

Replace both placeholder names before you run this block. Left as they are, the block fails with error 50000 on purpose, which is exactly what it does on a wrong target. On the right server and database, it returns nothing and the script moves on.

DECLARE @ExpectedServer sysname=N'SERVER\INSTANCE',@ExpectedDatabase sysname=N'DeployLab';
IF @@SERVERNAME<>@ExpectedServer OR DB_NAME()<>@ExpectedDatabase
    THROW 50000,'Wrong server or database. Reconnect to the approved target.',1;

Check the Required Objects and Level

Read compatibility_level and confirm the prerequisite table exists. Metadata visibility can hide an object from a limited caller, so test under the deployment identity. A prerequisite check should fail with a helpful message rather than letting an obscure later statement explain the missing requirement. Pre-flight checks must execute before the first mutation, including setup changes.

The compatibility check reads only the current database row in sys.databases, so it works under a login that sees just that database. A fresh database on my SQL Server 2025 test instance reported level 170 and passed.

In a fresh test database without dbo.RequiredTable, the second check fails with error 50002, and that failure is the intended result. Create the table, run the block again, and it returns nothing. A compatibility level below 160 raises error 50001 in the same way.

IF (SELECT compatibility_level FROM sys.databases WHERE database_id=DB_ID())<160
    THROW 50001,'The database compatibility level does not meet this script requirement.',1;
IF OBJECT_ID(N'dbo.RequiredTable',N'U') IS NULL
    THROW 50002,'The required table is missing or not visible.',1;

Inspect Available Volume Space

sys.dm_os_volume_stats exposes total and available bytes for volumes holding database files. Choose a threshold appropriate to the planned work. Current free space is a point-in-time observation, not a reservation. Which operation needs that capacity, and how much growth is approved?

The threshold in the block is 1,073,741,824 bytes, which is 1 GB, chosen only as an example. On my test server, the query returned one volume and the check passed. The DMV reports the volume under each database file, so a database with files on several drives returns one row per drive.

SELECT DISTINCT v.volume_mount_point,v.total_bytes,v.available_bytes
FROM sys.database_files AS f
CROSS APPLY sys.dm_os_volume_stats(DB_ID(),f.file_id) AS v;
IF EXISTS(SELECT 1 FROM sys.database_files f
          CROSS APPLY sys.dm_os_volume_stats(DB_ID(),f.file_id) v
          WHERE v.available_bytes<1073741824)
    THROW 50003,'Less than the example free-space threshold remains.',1;

Stop Later Batches When Pre-Flight Checks Fail

THROW ends the current batch, not the script. When I ran a guard file through sqlcmd without -b, the guard raised its error and the next GO batch still ran. With -b, or with :on error exit at the top, sqlcmd stopped at the first THROW and returned exit code 1. Pre-flight checks only protect later batches when the runner is told to stop on errors.

Read the error number and message before doing anything else. Each guard in this post uses its own number, so the output tells you which check failed. A permission problem, a missing table, and a wrong target need different fixes. Do not wrap the guards in TRY and CATCH that swallows the error, because the runner then sees a clean batch and keeps going.

Keep SET NOEXEC ON Away From THROW

A common guard puts SET NOEXEC ON inside the IF block, just before THROW. That order defeats the guard. SET NOEXEC ON takes effect at once, so the THROW after it is only compiled. When I tested that order, the batch finished with no message and exit code 0. It is an excellent way to demonstrate nothing with great confidence.

THROW cannot be followed by SET NOEXEC ON either, because THROW ends the batch first. So let THROW report the failure and let the runner stop the script. If a session ever does end up with NOEXEC on, run SET NOEXEC OFF or reconnect before the next attempt. Otherwise later tests only compile and appear to succeed.

Use SQLCMD Error Exit When Running a File

In SSMS SQLCMD mode, :on error exit provides another stop boundary for a script file. Keep the directive in a labeled command example so it is not mistaken for T-SQL. Use sqlcmd with -b for an automated execution path, so a failed guard also fails the job step or pipeline that called it.

Test the runner, not only the guard. Point the script at a wrong database on purpose and confirm that nothing after the first check runs. Then point it at the right target and confirm the checks stay silent. A guard that has never failed in a rehearsal has not yet been shown to work.

REM Command line: -b stops at the first error and returns a nonzero exit code.
sqlcmd -S "SERVER\INSTANCE" -d "DeployLab" -E -b -i "D:\Scripts\Deploy.sql"
REM In SSMS, turn on SQLCMD mode and make this the first line of the script:
REM :on error exit

Keep Pre-Flight Checks Ahead of All Writes

Pre-flight checks belong before CREATE, ALTER, or data changes, including helper-table creation. Repeat the checks in each independently runnable script. A guard is strongest when it validates the real prerequisites and when the runner has a tested response to its failure.

Keep the error numbers stable across releases. A deployment log that says error 50002 then points straight at the missing table check. When every script reuses the same numbers for the same kinds of checks, the operations team learns them quickly. The wording of a message can change, but the number stays the key.

A good guard block is boring to read and fast to run. Every check is a single IF with its own THROW number, and none of them changes anything. Put the block at the top of each script file, even when the files run in sequence, because someone will eventually run the third file alone.

Related reading on this blog: The Flight Connection Puzzle and Convert Old Syntax of RAISEERROR to THROW.

A pre-flight check is not a comment asking for care, it is an executable boundary before the change.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.