Active\Active Cluster Secondary Node

MarkJ · February 7, 2006 11:17AM

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?

petey · February 7, 2006 11:50AM

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.

MarkJ · February 8, 2006 4:23AM

Profiler confirms SQLBackup.exe is connecting to the correct instance. We are using SQL Server 2000 SP3 Enterprise Edition

petey · February 8, 2006 5:30AM

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?

MarkJ · February 8, 2006 5:36AM

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.

petey · February 8, 2006 5:41AM

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?

MarkJ · February 8, 2006 6:27AM

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

petey · February 8, 2006 6:30AM

And if you run Profiler on the default instance, do you see the query that checks for rights running on that instance?

MarkJ · February 8, 2006 6:46AM

I just see a very similar query to the one I just posted

petey · February 8, 2006 8:26AM

The query must be running on another instance. Is there a non-clustered instance running on any of the 2 nodes?

MarkJ · February 8, 2006 8:35AM

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.

petey · February 8, 2006 8:42AM

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.

MarkJ · February 8, 2006 8:50AM

I can now see this query running on the wrong instance - how do I fix that?

petey · February 8, 2006 8:54AM

Is the instance name identical to the cluster name?

MarkJ · February 8, 2006 8:56AM

Yes

Brian Donahue · February 8, 2006 9:52AM

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!

Active\Active Cluster Secondary Node

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk