1

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..

2
  • @TraderhutGames Yea unfortunately you're not going to get the full exception bubbled up with EXEC() / dynamic SQL. You'll need to build in your exception handling logic inside of the dynamic SQL. As an aside, you should always use EXEC sp_ExecuteSQL @DynamicSQLString instead of EXEC(@DynamicSQLString) for performance (and security) purposes. – J.D. Jan 7 at 1:11
  • Note that many of the options for the backup command do accept variables, so perhaps dynamic SQL isn't needed in the first place? – Tibor Karaszi Jan 7 at 9:34
1

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.