Help: SQL Server

Few days back one of my friend called me with an interesting problem. She told that when she does a regular restore of a database backup file, it works but when she does using TRY/CATCH block, it fails. The very first thing which I asked her– What is the error message? She sent me email with below message. I have changed some names to save the innocents.

<EMAIL>

Command which fails.

BEGIN TRY  
    RESTORE DATABASE [TestBackup] 
    FROM  DISK = N'C:\Temp\TestBackup.bak' 
    WITH  FILE = 1,  
    MOVE N'TestBackup' TO N'C:\Temp\TestBackup.mdf',  
    MOVE N'TestBackup_log' TO N'C:\Temp\TestBackup_log.LDF',  
    NOUNLOAD,  
    STATS = 5; 
END TRY 
BEGIN CATCH  
             THROW; 
END CATCH  

Here is the output

52 percent processed.

100 percent processed.

Processed 240 pages for database ‘TestBackup’, file ‘TestBackup’ on file 1.

Processed 2 pages for database ‘TestBackup’, file ‘TestBackup_log’ on file 1.

Converting database ‘TestBackup’ from version 611 to the current version 706.

Database ‘TestBackup’ running the upgrade step from version 611 to version 621.

Database ‘TestBackup’ running the upgrade step from version 621 to version 622.

Database ‘TestBackup’ running the upgrade step from version 622 to version 625.

Database ‘TestBackup’ running the upgrade step from version 625 to version 626.

Database ‘TestBackup’ running the upgrade step from version 626 to version 627.

Database ‘TestBackup’ running the upgrade step from version 627 to version 628.

Database ‘TestBackup’ running the upgrade step from version 628 to version 629.

Msg 102, Level 15, State 1, Procedure usp_logError, Line 11

Incorrect syntax near ‘200001’.

Msg 3013, Level 16, State 1, Line 11

RESTORE DATABASE is terminating abnormally

Command which works

RESTORE DATABASE [TestBackup] 
FROM  DISK = N'C:\Temp\TestBackup.bak' 
WITH  FILE = 1,  
MOVE N'TestBackup' TO N'C:\Temp\TestBackup.mdf',  
MOVE N'TestBackup_log' TO N'C:\Temp\TestBackup_log.LDF',  
NOUNLOAD,  
STATS = 5;

There is NO difference in restore command. Only difference is that it is wrapped in TRY block. If any exception raised, it would be caught and thrown.

<EMAIL>

Since I see version upgrade messages I asked if backup was taken on lower version and restore is performed on higher version? She informed that this backup is taken on SQL Server 2005 and she wants to restore on SQL Server 2012. I also asked her to share ERRORLOG and here is the snippet

2014-07-17 17:43:41.070 spid56 Error: 928, Severity: 20, State: 1.

2014-07-17 17:43:41.070 spid56 During upgrade, database raised exception 102, severity 25, state 1, address 00007FFCE2DC3E04. Use the exception number to determine the cause.

Exception 102 is “Incorrect Syntax” error. If we read the message in restore output carefully, we can see that there is a procedure usp_logError which seems to be a problem. I asked her to look at the code of usp_logError and search for 200001 and she found below:

raiserror 200001 'Debit and Credit Mismatch'

If we execute above piece of code in SQL 2005, it works and it’s valid whereas, it gives syntax error in SQL 2012. I looked into documentation and found below at http://msdn.microsoft.com/en-us/library/ms144262.aspx (Discontinued Database Engine Functionality in SQL Server 2014)

I asked her to change the syntax and modify the stored procedure before taking backup. Once done, the restore with and without TRY CATCH worked like a charm.

You can easily reproduce this error by creating below database and procedure in SQL 2005.

Create database TestBackup 
go 
use TestBackup 
go 
Create Procedure FailureProc 
as 
begin 
RAISERROR 20001 'ERROR RAISED' 
end 

Restoring this backup in SQL 2012 using TRY CATCH would fail.

Tip: Whenever I see any “Error: 928, Severity: 20, State: 1.” I always look for the exception code. In this case it was 102. To convert code to text, we can use sys.messages catalog view (check image below) . While searching further, I was able to find below variations.

2014-07-17 17:43:41.070 spid56 Error: 928, Severity: 20, State: 1.

2014-07-17 17:43:41.070 spid56 During upgrade, database raised exception 156, severity 25, state 1, address 00007FFCE2DC3E04. Use the exception number to determine the cause.

Error 156 – Incorrect syntax near the keyword ‘%.*ls’.

There might be many exceptions raised even without try…catch, you need to read the message and look for explanation of exception code.

You might ask, What should be done before upgrade to avoid such situations? You should run upgrade advisor. http://msdn.microsoft.com/en-us/library/ee210467.aspx (SQL Server 2014 Upgrade Advisor). There are versions available for SQL 2012 and earlier as well. They mostly catch all errors which might come after upgrade. Interestingly, RAISERROR problem is not caught by upgrade advisor.

http://connect.microsoft.com/SQLServer/feedback/details/708167/somewhat-incompatible-t-sql-on-sql-server-2012-rc0

http://connect.microsoft.com/SQLServer/feedback/details/694484/denali-ctp3-upgrade-advisor-misses-deprecated-raiserror-syntax

You can vote up if you think it would help.