Sql-server – How to know a backup command failed or worked in a script

backupsql serversql-server-2016t-sql

I have a command in a script to do a 'backup database [name] to [file] and all the other parameters.

Now, we have a problem where we run out of disk space, the script keeps on trucking, just issuing the message:
BACKUP DATABASE is terminating abnormally.

As if there is no problem… no exception is tossed, or anything… so the next command in the series, to do a restore of that database to another database… fails… and the script keeps on running…

Why doesn't it throw an exception when there is a catastrophic failure like either of these?

I've seen some suggestions that we should query log files to see if there was a database backup failure, but good grief, it should just DIE when you run the script.

Do I need to catch the exception, and throw the exception with a higher severity (I'd assume that the other one is thrown with the max severity, but maybe someone thought that a complete failure of a backup is just minor?)

I've seen other code / questions where people are doing a try/catch around the backup, but I have no interest in catching the exception, I want it to Abort right there, die, and report the error up the chain..

Instead, it just keeps going..

Best Answer

the problem is the error severity of the backup exception. I simulated your situation with this script:

BACKUP DATABASE [test] TO  DISK = N'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE [test] TO  DISK = N'C:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

The first command generate an error of severity 16 (path not found)

Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'. Operating system error 3(path not found.). Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.

and then sql server continues to execute the next command:

11 percent processed. 21 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 61 percent processed. 71 percent processed. 80 percent processed. 90 percent processed. 100 percent processed. Processed 504 pages for database 'test', file 'test' on file 2. Processed 2 pages for database 'test', file 'test_log' on file 2. BACKUP DATABASE successfully processed 506 pages in 0.029 seconds (136.179 MB/sec). Completion time: 2021-01-07T14:06:19.6844320+01:00

As you can see here: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-ver15#levels-of-severity

The error severity 16 do not breaks the batch. But if you use XACT_ABORT ON...

    SET XACT_ABORT ON

    BACKUP DATABASE [test] TO  DISK = N'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak' 
    WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

    BACKUP DATABASE [test] TO  DISK = N'C:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak' 
    WITH NOFORMAT, NOINIT,  NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

The second backup isn't ever reached.

Msg 3201, Level 16, State 1, Line 3 Cannot open backup device 'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'. Operating system error 3(Impossibile trovare il percorso specificato.). Msg 3013, Level 16, State 1, Line 3 BACKUP DATABASE is terminating abnormally. Completion time: 2021-01-07T14:34:39.0700755+01:00