Active\Active Cluster Secondary Node

MarkJMarkJ Posts: 16
edited February 8, 2006 9:52AM in SQL Backup Previous Versions
I have installed SQL Backup on to the two servers in our cluster. Our setup consists of a default instance of SQL server running on one node and a named instance running on the other. The Default instance installed and is working. The named instance appears to have installed and recognised the correct instance of SQL Server. It does not, however, back anything up. I get the following error message

Error 880: BACKUP DATABASE permission denied in database: (Database2)
SQL error 911: Could not locate entry in sysdatabases for database 'Database2'. No entry found with that name. Make sure
that the name is entered correctly.

SQL Backup exit code: 880
SQL error code: 911

Database2 does exist in sysdatabases and the Database user has rights to back up the database. Can anybody help with this?

Comments

  • peteypetey Posts: 2,358 New member
    I'm not sure if the service is recognising the named instance correctly. It's probably connecting to the default instance, where Database2 does not exist. Could you pls use Profiler to confirm this?

    Are you using SQL Server 2005? If so, there is a bug in SQL Backup 4.0 that prevents it from working correctly with certain configurations of named instances on clusters. Version 4.1 fixes this bug.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Profiler confirms SQLBackup.exe is connecting to the correct instance. We are using SQL Server 2000 SP3 Enterprise Edition
  • peteypetey Posts: 2,358 New member
    Do you encounter the same error if you run the backup from Query Analyzer? Also, is your server set up with a case sensitive collation?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I do encounter the same error if I run the backup from Query Analyzer and our server is not set up with a case sensitive collation. If I run an ordinary SQL Server backup from Query Analyzer it works.
  • peteypetey Posts: 2,358 New member
    In the Profiler trace, when you run the backup command on the named instance, do you see the SQL Backup query that runs to check for backup rights running? What is the result if you run that query from within Query Analyzer?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • The names have been changed to protect the innocent but the following is the only query I see is:

    exec sp_executesql N'exec sp_executesql @P1', N'@P1 nvarchar(223)', N'exec master..sqlbackup N''-SQL "BACKUP DATABASE [Database2] TO DISK = ''''\\sql\BackUps\FULL_(local)_Database2_20060207_153832.sqb'''' WITH NAME = ''''<AUTO>'''', DESCRIPTION = ''''<AUTO>'''', COMPRESSION = 3"'''


    The response to this is:

    Backing up Database2 (full database) to:
    \\sql\BackUps\FULL_(local)_Database2_20060207_153832.sqb

    Error 880: BACKUP DATABASE permission denied in database: (Database2)
    SQL error 911: Could not locate entry in sysdatabases for database 'Database2'. No entry found with that name. Make sure
    that the name is entered correctly.

    SQL Backup exit code: 880
    SQL error code: 911


    exitcode 880
    sqlerrorcode 911
    filename01 \\sql\BackUps\FULL_(local)_Database2_20060207_153832.sqb
  • peteypetey Posts: 2,358 New member
    And if you run Profiler on the default instance, do you see the query that checks for rights running on that instance?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I just see a very similar query to the one I just posted
  • peteypetey Posts: 2,358 New member
    The query must be running on another instance. Is there a non-clustered instance running on any of the 2 nodes?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Can you tell me the name of the query that checks for rights so I can specifically filter profiler for that. What I haven't told you is these are all busy production servers and I am filtering profiler output.
  • peteypetey Posts: 2,358 New member
    You should be able to filter by application name. Look for SQBCoreService.exe. The query itself has a comment at the top that says

    -- Testing for BACKUP rights.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I can now see this query running on the wrong instance - how do I fix that?
  • peteypetey Posts: 2,358 New member
    Is the instance name identical to the cluster name?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Mark,

    I've sent you an email. I believe there is a fix I can send you for this. An issue we'd identified with named instances where the instance name is identical to the server name also applies to multiple virtual SQL Servers running on the same clustered server. It causes the backup job to be executed on the wrong instance. In the case of clusters, this translates to 'the wrong server'.

    Please let me know if you don't get my email!
Sign In or Register to comment.