Linux bash script: How can I detect an error on a RESTORE DATABASE query on msssql-server?

Syncing a windows app with a linux website, I use a bash script which transfers each day a MS-SQL database from a Windows machine to a Linux installation, and uploads it to the mssql-server with the code below:

sqlcmd -b -S localhost -U SA -P <password> -Q "RESTORE DATABASE <dbname> FROM DISK = '/home/<user>/$newdb' WITH MOVE '<x_Data>' TO '/var/opt/mssql/data/<dbname>.mdf', MOVE 'x_Log' TO '/var/opt/mssql/data/<dbname>_Log.ldf'"

Most of the times this line works perfectly. But there are instances in which restoration fails with an error like the following: “Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.”

I can’t seem to find a way to detect these errors and make the script respond accordingly by repeating the process or restarting the mssql-server.

Does anyone knows how to detect the failed query response?

Thanks in advance!

Answer

You’re already using the -b flag to terminate the job if it fails. This sets the exit status of that command to non-zero. (A bit like how %ERRORLEVEL% works on Windows.)

The last command’s exit status can be accessed in a bash script with $?.

Something like this could work:

sqlcmd -b ...
if [[ $? -ne 0 ]]; then
  echo "SQL upload failed"
  mail -s "sql error!" "[e-mail]" "sql broke again"
  exit 1
fi

It can of course be shortened. Even something like (sqlcmd -b ...) || (echo "Failed"; exit 1) might work for you.