Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Archive for the ‘Error’ 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 SETUP – The syntax of argument "/SKIPRULES" is incorrect

    Posted by blakhani on April 2, 2015


    While running SQL Server setup I have faced below error today. You may not get this error unless you run it from command line.

     

    here is the text of the message.

    TITLE: SQL Server Setup failure.
    ——————————
    SQL Server Setup has encountered the following error:

    The syntax of argument "/SKIPRULES" is incorrect. Either the delimiter ‘=’ is missing or there is one or more space characters before the delimiter ‘=’. Please use /? to check usage.
    Error code 0x84B40001.
    For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0xCAE17AB9%25400x2841E06E%25401204%25401

    ——————————
    BUTTONS:
    OK
    ——————————

     

    Here is the command which I was trying

    setup.exe Action=/InstallFailoverCluster /SkipRules = "Cluster_VerifyForErrors"

    Error message is very clear in telling the problem with the command. If we read complete message with patience, it says there is a space. Here is the correct version of command. Note that I have removed space after SkipRules Parameter and “=”.

    setup.exe Action=/InstallFailoverCluster /SkipRules="Cluster_VerifyForErrors"

    The reason I have to go with command line setup is because I was not able to install SQL Cluster from UI and here was the message.

     

    Hope this helps.

    Posted in Error, Installation | Tagged: , , | Leave a Comment »

    Solution – Getting Error while running rebuild index on CRM database – Unable to process object ID <ID> (object <Name> because it is a synonym. If the object referenced by the synonym is a table or view, retry the operation using the base object that the synonym references."

    Posted by blakhani on February 11, 2015


    While rebuilding all indexes on a CRM database, one of my friend reported below error.

    Msg 2547, Level 16, State 1, Line 15
    Unable to process object ID 2007014231 (object "AttributeTypes") because it is a synonym. If the object referenced by the synonym is a table or view, retry the operation using the base object that the synonym references."

    First let’s understand the meaning of the error. The error message clearly says that error is due to the fact that we are running DBCC command for a synonym which is not possible. we need to run that on base object. Here is the simple repro of the error.

    use tempdb
    go
    if object_id('TestTable') is not null
    drop table TestTable
    go
    if object_id('TestTableSyn') is not null
    drop synonym TestTableSyn
    go
    create table TestTable (i int)
    go
    CREATE SYNONYM [dbo].[TestTableSyn] FOR [dbo].TestTable
    GO
    dbcc DBREINDEX('TestTableSyn')
    go
    --dbcc CHECKTABLE('TestTableSyn')
    --go
    

    You would get below error in the result.

    Msg 2547, Level 16, State 1, Line 15

    Unable to process object ID 2007014231 (object "AttributeTypes") because it is a synonym. If the object referenced by the synonym is a table or view, retry the operation using the base object that the synonym references."

     

    Let’s have a look at script what he was using.

    USE DBName   --Enter the name of the database you want to reindex 
    
    DECLARE @TableName varchar(255) 
    
    DECLARE TableCursor CURSOR FOR 
    SELECT table_name FROM information_schema.tables 
    WHERE table_type = 'base table' 
    
    OPEN TableCursor 
    
    FETCH NEXT FROM TableCursor INTO @TableName 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
    PRINT 'Reindexing ' + @TableName 
    DBCC DBREINDEX(@TableName,' ',90) 
    FETCH NEXT FROM TableCursor INTO @TableName 
    END 
    CLOSE TableCursor 

    Do you see any problem? It looks good but notice that schema name is not considered in the script. I executed below and that would explain the cause.

    Select * from sys.objects where name = 'AttributeTypes'
    

    Looking at output, we can tell that there are two objects with same name in two different schemas (1 and 5). Schema 1 would be dbo and 5 was Metadata. In the script which he was using, the schema name was not considered. Due to this DBCC DBREINDEX is running for dbo.AttributeTypes which is synonym (2007014231)and due to that we are seeing error.

    I provided below modified script.

    DECLARE @TableName SYSNAME
            ,@schema_name SYSNAME
            ,@fullname VARCHAR(8000)
    DECLARE TableCursor CURSOR
    FOR
    SELECT  TABLE_NAME
            ,TABLE_SCHEMA
    FROM information_schema.tables
    WHERE table_type = 'base table'
    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName ,@schema_name
    WHILE @@FETCH_STATUS = 0
    BEGIN
            SET @fullname = @schema_name + '.' + @TableName
            PRINT @fullname
            DBCC DBREINDEX (@fullname,' ',90)
            FETCH NEXT FROM TableCursor INTO @TableName ,@schema_name
    
    END
    CLOSE TableCursor
    DEALLOCATE TableCursor
    go
    
    

    I tested the script and it worked fine for CRM database. Same issue was blogged by my colleague John over here and it has some more suggestions to try.

    On a side note, ideally you should use Maintenance plan available with SQL Server to rebuild the indexes.

    Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Posted in Error, SQL Server | Tagged: , , , | 3 Comments »