Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,050,840 hits

  • Select GETDATE()

    June 2023
    M T W T F S S
     1234
    567891011
    12131415161718
    19202122232425
    2627282930  

Archive for the ‘Error’ Category

« Previous Entries

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 AlwaysOn – Paperback, Kindle
    • Advertisement

    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 »

    « Previous Entries