Unable to connect to SQL Server 2005 from Backupexec application
Problem Description:
===============
While connecting to SQL Server 2005 Express instance through BackupExec application, we might encounter a connection failure. And the following error message would be logged in the Application event viewer logs
The Backup Exec Device and Media Service could not start because the database recovery has failed.
Refer to the database recovery log for details. For more information, click the following link:
https://eventlookup.veritas.com/eventlookup/EventLookup.jhtml
Resolution :
=========
· From the error message, we see that media service could not start as the recovery failed on a database
· Checked the SQL Server ERRORLOG and found the following errors being logged
//////////ERRORLOG//////////
2009-08-15 17:03:11.54 spid17s Starting up database 'BEDB'.
2009-08-15 17:06:00.85 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
2009-08-15 17:06:31.90 spid12s Service Broker manager has shut down.
2009-08-15 17:06:31.98 spid12s Error: 17054, Severity: 16, State: 1.
2009-08-15 17:06:31.98 spid12s The current event was not reported to the Windows Events log. Operating system error = 1717(The interface is unknown.). You may need to clear the Windows Events log if it is full.
2009-08-15 17:06:32.78 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2009-08-15 17:06:32.95 Server The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x862, state: 3. Administrator should deregister this SPN manually to avoid client authentication errors.
· Found only one user database 'BEDB' on the server and this is the database used by BackupExec
· Since the application event viewer logs stated that the database recovery is failing, suspected that the recovery of database 'BEDB' had failed
· Hence, tried restarting the SQL Server service.
· Found no errors in the ERRORLOG and all the system and user databases are recovered without any errors
· At first look it seemed to be a data integrity issue with BEDB database. Hence decided to run DBCC CHECKDB('BEDB') and check the consistency of the database.
· The results turned out to be clean and we confirmed that there is no corruption in the database.
DBCC results for 'MachineDevice'.
There are 0 rows in 0 pages for object "MachineDevice".
DBCC results for 'ChangeJournalData'.
There are 0 rows in 0 pages for object "ChangeJournalData".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'BEDB'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
· The recovery of the database 'BEDB' completed without any issues. Hence the error message mentioned in subject is misleading.
· Took hints from below mentioned article in Symantec website which talks about a file called ‘dbrecover.log’ located at C:\Program Files\Symantec\Backup Exec\Logs\ and this is used by Backupexec application for logging errors.
https://seer.entsupport.symantec.com/docs/294396.htm
· Looked into dbrecover.log and found the following logs in it
//////// dbrecovery.log //////////
Backup Exec Database Recovery
Recover database using best method..
GetDatabaseStatus
OpenFromInitializationString Connection String = Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=SAFE1\BkupExec;Locale Identifier=1033;Application Name=BEWS DBUTIL hr=0x8007007e
Error connecting to master database: hr = 0x8007007e
OS ERROR: 0x8007007e (-2147024770)
Status of database BEDB is unknown
OS ERROR: 0x8007007e (-2147024770)
Status of database BEDB is unknown
OS ERROR: 0x8007007e (-2147024770)
· 0x8007007e translates to “The specified module could not be found.”
· BackupExec is unable to connect to SQL Server instance using SQLOLEDB provider
· Checked the UDL connectivity on the server by using the 'Microsoft OLEDB provider for SQL Server' and got the following error
"Provider is no longer available. Ensure that the provider is installed properly"
Steps to perfom UDL Test
1. Create a new empty text file like "test.txt"
2. Rename the file as "test.udl"
3. As soon as you renamed the file, text file icon should change to a UDL icon
4. Double click on test.udl file and you'll receive a window titled "Data Link Properties"
5. You'll get all the installed OLE DB Providers on the box when you've switched to the Provider tab.
6. Select the 'Microsoft OLEDB provider for SQL Server and click on "Next" and the context will switch to "Connection" tab.
7. Provide the <machine name>\<instance name> for SQL server and enter the default database name and click Test connection
· Ran Component checker (https://www.microsoft.com/downloads/details.aspx?familyid=8F0A8DF6-4A21-4B43-BF53-14332EF092C9&displaylang=en) tool to check if there is any mismatch between the DLLs found on the server
· Found that SQLOLEDB.dll and SQLOLEDB.rll were missing under the following folder
o C:\Program Files\Common Files\System\Ole DB\
· Downloaded MDAC 2.8 SP1 from below mentioned link and reinstalled MDAC 2.8 SP1
· Tried to run the backupexec utility now and it connected to SQL Server without any errors.
Aravind Lakshminarayanan
SE, Microsoft SQL Server
Reviewed by
Shamik Ghosh & Sudarshan Narasimhan
TL, Microsoft SQL Server