Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Posts Tagged ‘restore error’

Tips and Tricks : Database restore will fail in STANDBY mode

Posted by blakhani on August 5, 2014


One of the various methods to move user database from lower version to higher version is backup restore. I have seen various upgrade scenarios using log-shipping and database mirroring as well. In past few days, I have seen similar question from at least three friend and I was surprised. Below are the questions:

  • We are planning a move, our production database from server A (which is SQL2005) to our new server B (which is SQL2008 R2). We are thinking and testing setting up logshipping between these servers in standby mode. This is because few applications need only to read the data.
  • If my primary server is 2008 (standard enterprise edition) and 2008 r2 (standard edition), then wil it possible to restore 2008 databse back-up files on 2008r2 database using standby mode recovery model

When we try to restore in standby mode to higher version, we would get below error message

Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

This is applicable to any lower to higher version restore. The standby mode restore is not allowed because the system level information (table, columns) might get change across version. If we perform backup from SQL 2012 and restore on SQL 2014 (using “WITH RECOVERY”, we can see below messages.

64 percent processed.
100 percent processed.
Processed 0 pages for database ‘MyDatabase’, file ‘M’ on file 2.
Processed 13 pages for database ‘MyDatabase’, file ‘M_log’ on file 2.

Converting database ‘MyDatabase’ from version 706 to the current version 782.

Database ‘MyDatabase’ running the upgrade step from version 706 to version 770.
Database ‘MyDatabase’ running the upgrade step from version 770 to version 771.
Database ‘MyDatabase’ running the upgrade step from version 771 to version 772.
Database ‘MyDatabase’ running the upgrade step from version 772 to version 773.
Database ‘MyDatabase’ running the upgrade step from version 773 to version 774.
Database ‘MyDatabase’ running the upgrade step from version 774 to version 775.
Database ‘MyDatabase’ running the upgrade step from version 775 to version 776.
Database ‘MyDatabase’ running the upgrade step from version 776 to version 777.
Database ‘MyDatabase’ running the upgrade step from version 777 to version 778.
Database ‘MyDatabase’ running the upgrade step from version 778 to version 779.
Database ‘MyDatabase’ running the upgrade step from version 779 to version 780.
Database ‘MyDatabase’ running the upgrade step from version 780 to version 781.

Database ‘MyDatabase’ running the upgrade step from version 781 to version 782.

RESTORE LOG successfully processed 13 pages in 0.088 seconds (1.109 MB/sec).

As we can see in the sequence, first files are restored and then recovery process has upgraded version of database. What this step would do? There are few system tables within the database are altered accommodate changes due to new features/fixes etc. These changes are done as a part of “upgrade”. Few things can be seen via profiler but not everything. This version value can be seen in sys.sysdatabases. I have run query in SQL 2012 and SQL 2014 which is same as version seen in restore command (706 and 782)

image

In summary, WITH STANDBY is only supported when both SQL Servers are the same version. We can “see” the database table only by performing recovery. An alternative, if we need read access to the destination databases, is to use replication.

Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Tips and Tricks | Tagged: , , , , , | 3 Comments »

    Tips and Tricks : Restore Using TRY…CATCH might fail with error

    Posted by blakhani on July 22, 2014


    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)

    Transact-SQL

    RAISERROR in the format RAISERROR integer ‘string’ is discontinued.

    Rewrite the statement using the current RAISERROR(…) syntax.

     

    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.


    image

    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.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Error, Upgrade | Tagged: , , , , , | 2 Comments »