I have been running through some backup and restore scenarios using BizTalk 2004 and SQL Server 2000 today, which have been quite time consuming. We were testing a scenario whereby an orchestration had a number of dehydrated instances and we would then destroy the database, restore it and then attempt to resume the orchestration instances.
Well the tests had completed with a success. However, I was receiving the following error during the Backup BizTalk Server SQL Server Agent Job:
Executed as user: <DOMAIN USER>. OLE DB provider 'MSDASQL' reported an error. [SQLSTATE 42000] (Error 7399) Cannot open database requested in login 'BizTalkRuleEngineDb'. Login fails.] [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.
After a rather long and laborious process of going through the security and stepping through various stored procedures it turned out that the call to sp_BuildFullBackupString was causing the error. The reason being that in this instance of it being called it was attempting to use BizTalkRulesEngineDb for the database, but it did not exist. The actual database is BizTalkRuleEngineDb not BizTalkRulesEngineDb and our restore had used an incorrect name.
I thought I would post this on my blog in case anyone comes across the error above in a similar situation. Check that the database exists first before going on a debugging session. Lesson learned.