Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Archive for the ‘Restore’ Category

Restore ERROR : This backup cannot be restored using WITH STANDBY because a database upgrade is needed

Posted by blakhani on May 21, 2015


One of my colleague in office sent an email to find the cause of the error. He wanted to configure Log-Shipping with secondary on Standby mode. Rather than “reply to all”, I generally ping the person directly to tell the possible reason of error. Here is what we discussed (let’s assume that his name is Manoj – M)

B: Hi Manoj
M: Hello
B: Saw you email to DL about log shipping problem.
M: Oh yeah. Thanks for reaching out.
B: No problem.
B: Are you restoring database from lower version to higher version of SQL?
M: No, both are 2008.
B: That’s doesn’t sound right. Can you please run Select @@version command in Management Studio on both the server and share the output?
M: Sure. Give me a minute to connect and run the query.
B: Sure

<1 min pause>

M: There you go

Source:
Microsoft SQL Server 2008 (SP1) – 10.0.2573.0 (X64)
Feb 4 2011 11:27:06
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Destination:
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

 

B: Okay. that is the problem. You are taking backup from SQL 2008 and restoring on SQL 2008 R2 in standby mode.
M: Both are not 2008?
B: No, destination is 2008 “R2” which is the next release after 2008.
M: Oh.. I thought its like a service pack on top of SQL 2008.
B: Unfortunately, its not a service pack. It’s fresh release having some enhancements.
M: Okay. So error message is correct.
B: Yeah. If you want to use secondary for reporting then you have to use standby mode. This can only be done on destination which has same version of SQL. Or you need to upgrade current primary server.
M: Hmm.. I got it now. Let me check with my DBA team and management to decide the next action.
B: Sure Manoj. Is there any other clarification you might need?
M: No Balmukund. I am good at this point. You have been very helpful. Appreciate it.
B: My pleasure. Bye for now.
M: Bye!

In summary, Here is what you would get in SSMS if we try to restore a backup from lower version of SQL to higher version of SQL in standby mode.

TITLE: Microsoft SQL Server Management Studio
——————————
SQL Server Management Studio restore database ‘DabbaWala’
——————————
ADDITIONAL INFORMATION:
Restore failed for Server ‘Destination’.  (SqlManagerUI)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
——————————
System.Data.SqlClient.SqlError: This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476
——————————
BUTTONS:
OK
——————————

 

The error is raised because during restore from lower to higher version the database version needs an upgrade to match with system table (or any other) change in higher version. Here is the query to see the version I am talking about.

Select name, version from sys.sysdatabases

This version upgrade is part of recovery process and we can’t pause recovery in middle and view the database state (that’s what is done in standby mode). So, the only way to restore that would be to use “with recovery” or “with norecovery” option.

Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in Error, Logshipping, Restore, SQL Server | Tagged: , , | 2 Comments »

    SQL Server Internals : What is TUF File in SQL Server?

    Posted by blakhani on July 24, 2014


    While answering on few forums I have realized that there are some variation of same question which I used in title of the blog.

    • What is .TUF file in Log Shipping?
    • I deleted TUF file, any problem?
    • I am not using log-shipping, still I see a TUF file.
    • What is the location of TUF file in log shipping?

    TUF file stands for Transaction Undo File in SQL Server. I will try to explain little more so that you can understand the usage and importance of it.

    When we perform a restore of database backup files, we have three options.

    • with recovery – database would be recovered and no further backups can be applied.
    • with norecovery – database would not be recovered and further backups can be restored. NOT be accessible to users for read operations.
    • with standby– database would not be recovered and further backups can be restored. Accessible to users for read operations.

    If SQL has to allow read operation to users, it has to show only those committed data. Let’s assume a scenario to understand better.

    1. Created Employee Table.
    2. Inserted 5 employees and transaction committed.
    3. Took full backup which got completed. (F1)
    4. Started Transaction and Inserted 5 more employees
    5. Took Transaction log backup and completed (T1)
    6. Committed Transaction which was started in step 4.
    7. Took Transaction log backup and completed (T2)

    If we perform restore operation of F1, we have to use either norecovery or standby because if we use “with recovery”, we won’t be able to restore other backups. Lets assume that we use “with standby”. This time a TUF file is created but would not contain much information because there is no uncommitted transaction when backup was taken. Since we are able to open database, we should be able to see 5 employee. So far, we are clear. Next, if we go ahead and apply T1 with standby and then open the database, should we see those new 5 records also? No, we should not because they were part of a transaction that was uncommitted when T1 was completed. The information stays in TUF file. When the next transaction log backup T2 is restored, SQL Server would use this TUF file along with T2 to roll forward that particular transaction. So we need both the files during restore. Now you can imagine a situation and implication of missing a TUF file. Yes, you would not be able to restore the log backups chain and need a fresh backup to be taken.

    In log shipping scenario if we choose “Standby” option for the secondary server then the transaction log backup file would be restored in standby mode. As discussed earlier, this would need a TUF file to be generated automatically. The location of this file is not configurable and it has been changed in few versions of SQL. Refer KB 

    Now a major question would be, how can I find the location of the TUF file given in last restore? I use a little trick here. If you are aware of default trace feature in SQL Server, you might know the restore command is logged into default trace. I have taken this idea from Standard Reports in SSMS.

    declare @enable int;
    select @enable = convert(int, value_in_use) from sys.configurations where name = 'default trace enabled' 
    print 'default trace is NOT enabled.'
    if @enable = 1  
    begin 
            declare @curr_tracefilename varchar(500);
            declare @base_tracefilename varchar(500);
            declare @status int; 
            declare @indx int;   
            declare @temp_trace table ( 
                    Error int
            ,       StartTime datetime
            ,       HostName sysname collate database_default null
            ,       ApplicationName sysname collate database_default  null
            ,       LoginName sysname collate database_default null
            ,       Severity int
            ,       DatabaseName sysname collate database_default null
            ,       TextData nvarchar(max) collate database_default 
            ); 
            
            select @status=status, @curr_tracefilename=path from sys.traces where is_default = 1 ;
            set @curr_tracefilename = reverse(@curr_tracefilename) 
            select @indx  = patindex('%\%', @curr_tracefilename)  
            set @curr_tracefilename = reverse(@curr_tracefilename) 
            set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc'; 
    
            
            select  StartTime
            ,        TextData  
            ,       HostName
            ,       ApplicationName
            ,       LoginName
            ,       DatabaseName    
            from ::fn_trace_gettable( @base_tracefilename, default ) 
            where EventClass = 115
            and TextData like '%Restore%Standby%' 
            and ServerName = @@servername
            order by StartTime
    
    end
    

     

    Here is the output from my SQL Instance.

    image

    If the undo file is missing, SQL Server would raise below message. Note that TUF extension is not mandatory.

    Msg 3013, Level 16, State 1, Line 8

    RESTORE LOG is terminating abnormally.

    Msg 3441, Level 17, State 1, Line 8

    During startup of warm standby database ‘TestBackup’ (database ID 6), its standby file (‘E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestBackup_RollbackUndo_2014-07-24_06-58-39.bak’) was inaccessible to the RESTORE statement. The operating system error was ‘2(The system cannot find the file specified.)’. Diagnose the operating system error, correct the problem, and retry startup.

     

    Hope this clears some questions you might had before reading this blog. For more, you can post comment and I shall try to answer.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in backup, Restore, SQL Server | Tagged: , , | 11 Comments »