RESTORE DATABASE FAILED: HRESULT m_hr = 0x80040e14

I need help understanding why a RESTORE DATABASE operation with SQL Server 2008 Express is failing with an HRESULT code of 0x80040e14.

Overview of Operation Environment

We have a Point of Sale application running on several, cooperating terminals whose architecture is to have a Master Terminal with all of the operational data and zero or more Satellite Terminals which update the operational data as transactions are processed.

There is an alternative architecture in which a Backup Master Terminal is part of the group of terminals. There is a command to synchronize the Master Terminal and the Backup Master Terminal operational data as part of setting up a group or performing a replacement of a Backup Master Terminal.

Most of the operational totals data is stored in a set of flat files on disk however we are using Microsoft SQL Server Express to hold the PLU totals.

As part of the synchronization operation, we also transfer a SQL Server database backup. The RESTORE DATABASE operation is failing and I need help to figure out the problem.

The application is written in C++ and is using ADO for the SQL Server interface.

The test setup is using Microsoft SQL Server Express 2008 R2 which was installed using the Default Instance in order to be compatible with the original install of SQL Server 2000 Express years ago. The POS application specifies the Default Instance in the connect string.

Source code of functionality Used

The Restore function is as follows:

ULONG   CnPluTotalDb::RestoreDB(LPCTSTR szSQlCode){

    CString szSQL;
    USHORT usError = 0;
    USHORT usErrorCount = 0;

    CString activeConn;
    activeConn.Format(ConnectionStringTemp,L"master");
    _bstr_t strConnect = activeConn;

    //We set the database to be in single user mode while we do this database reset
    //so that no one else can access the database information while we reset the DB
    //SR 725 JHHJ
SINGLE_USER_DB:
    szSQL.Format(L"ALTER DATABASE %s SET SINGLE_USER WITH ROLLBACK IMMEDIATE", szSQlCode);
    m_hr = __pRecO->OpenRec(CnVariant(szSQL), strConnect, adOpenForwardOnly,adLockOptimistic,adCmdText);

    //Close the recordset object so we can open a new one 
    //for the next SQL command
    __pRecO->Close();

    if (FAILED(m_hr))
    {
        char  xBuff[128];
        sprintf(xBuff, "CnPluTotalDb::RestoreDB() SET SINGLE_USER FAILED: HRESULT m_hr = 0x%8.8x", m_hr);
        NHPOS_ASSERT_TEXT(0, xBuff);
        usError = 1;
        goto MULTI_USER_DB;
    }

    //We will drop the database because the information in it is out of date
    //so that we can restore it in the next call.
    szSQL.Format(L"DROP DATABASE %s", szSQlCode);
    m_hr = __pRecO->OpenRec(CnVariant(szSQL), strConnect, adOpenForwardOnly,adLockOptimistic,adCmdText);
    __pRecO->Close();

    if (FAILED(m_hr))
    {
        char  xBuff[128];
        sprintf(xBuff, "CnPluTotalDb::RestoreDB() DROP DATABASE FAILED: HRESULT m_hr = 0x%8.8x", m_hr);
        NHPOS_ASSERT_TEXT(0, xBuff);
    }


    //Restore the database from the file that the master/backupmaster has just sent over.
    szSQL.Format(PLUTOTAL_DB_BACKUP_RESTORE, szSQlCode, szSQlCode);
    m_hr = __pRecO->OpenRec(CnVariant(szSQL), strConnect, adOpenForwardOnly,adLockOptimistic,adCmdText);
    __pRecO->Close();

    if (FAILED(m_hr))
    {
        char  xBuff[128];
        sprintf(xBuff, "CnPluTotalDb::RestoreDB() RESTORE DATABASE FAILED: HRESULT m_hr = 0x%8.8x", m_hr);
        NHPOS_ASSERT_TEXT(0, xBuff);
    }

MULTI_USER_DB:
    //Return the database back to multi-user mode. 
    szSQL.Format(L"ALTER DATABASE %s SET MULTI_USER", szSQlCode);
    m_hr = __pRecO->OpenRec(CnVariant(szSQL), strConnect, adOpenForwardOnly,adLockOptimistic,adCmdText);

    __pRecO->Close();   // one shot!!!

    if (FAILED(m_hr))
    {
        char  xBuff[128];
        sprintf(xBuff, "CnPluTotalDb::RestoreDB() SET MULTI_USER FAILED: HRESULT m_hr = 0x%8.8x", m_hr);
        NHPOS_ASSERT_TEXT(0, xBuff);
        usErrorCount++;
        if (usErrorCount < 5) goto MULTI_USER_DB;
        return PLUTOTAL_E_FAILURE;
    } else if (usError)
    {
        usError = 0;
        goto SINGLE_USER_DB;
    }

    return PLUTOTAL_SUCCESS;
}

The connect string template used is:

ConnectionStringTemp = L"Provider=MSDASQL;DRIVER={SQL Server};SERVER=lpc:(local);DATABASE=%s;UID=; Password=;";

The defines used for the parameters are:

#define PLUTOTAL_DB_BACKUP_PATH     L"C:\\TempDisk\\NCR\\Saratoga\\Database\\"    // was _T("C:\\Program Files\\Microsoft SQL Server\\MSSQL\\Data\\")
#define PLUTOTAL_DB_BACKUP_RESTORE  L"RESTORE DATABASE %s FROM DISK = 'C:\\TempDisk\\NCR\\Saratoga\\Database\\%s_BAK.dat' WITH REPLACE"
#define PLUTOTAL_DB_BACKUP_BACKUP   L"BACKUP DATABASE %s TO DISK = 'C:\\TempDisk\\NCR\\Saratoga\\Database\\%s_BAK.dat' WITH INIT"

Observed Behavior and Logs Generated

I can see that the BACKUP DATABASE operation on the Master Terminal does generate a backup file and that file is transferred to the Backup Master Terminal.

It also looks like the Backup Master is going into Single User Mode and the DROP DATABASE is working.

The logs generated showing the errors are as follows:

PluTtlDb.cpp,   440, CnPluTotalDb::RestoreDB() RESTORE DATABASE FAILED: HRESULT m_hr = 0x80040e14                                            
 PluTtlDb.cpp,   454, CnPluTotalDb::RestoreDB() SET MULTI_USER FAILED: HRESULT m_hr = 0x80040e14                                              
 PluTtlDb.cpp,   454, CnPluTotalDb::RestoreDB() SET MULTI_USER FAILED: HRESULT m_hr = 0x80040e14                                              
 PluTtlDb.cpp,   454, CnPluTotalDb::RestoreDB() SET MULTI_USER FAILED: HRESULT m_hr = 0x80040e14                                              
 PluTtlDb.cpp,   454, CnPluTotalDb::RestoreDB() SET MULTI_USER FAILED: HRESULT m_hr = 0x80040e14                                              
 PluTtlDb.cpp,   454, CnPluTotalDb::RestoreDB() SET MULTI_USER FAILED: HRESULT m_hr = 0x80040e14                                              
 TtlPluBk.c,   190, **WARNING:  TTL_BKUPPLUDBFAIL  PluTotalRestoreDB() returned ulSts 9999 0x270f                                           

Edit A: After adding additional logs in the method OpenRec() to obtain more detailed error information what turns out to be a fairly general purpose HRESULT, I have the following new information. These logs have some repeated text since the error description exceeds the error log limit of 110 characters so most error descriptions are logged with two log API calls to log the first half and then second half of the error description.

 , rce\nhpos\plutotal\CnAdoXP.h,   342, IDispatch error #3092                                                                                                   
 , rce\nhpos\plutotal\CnAdoXP.h,   345, [Microsoft][ODBC SQL Server Driver][SQL Server]The media family on device 'C:\TempDisk\NCR\Saratoga\Database\PluTtlDC_BA
 , rce\nhpos\plutotal\CnAdoXP.h,   349, sk\NCR\Saratoga\Database\PluTtlDC_BAK.dat' is incorrectly formed. SQL Server cannot process this media family.          
 ,                 PluTtlDb.cpp,   440, CnPluTotalDb::RestoreDB() RESTORE DATABASE FAILED: HRESULT m_hr = 0x80040e14                                            
 , rce\nhpos\plutotal\CnAdoXP.h,   342, IDispatch error #3092                                                                                                   
 , rce\nhpos\plutotal\CnAdoXP.h,   345, [Microsoft][ODBC SQL Server Driver][SQL Server]User does not have permission to alter database 'PluTtlDC', the database 
 , rce\nhpos\plutotal\CnAdoXP.h,   349, database 'PluTtlDC', the database does not exist, or the database is not in a state that allows access checks.          
 ,                 PluTtlDb.cpp,   454, CnPluTotalDb::RestoreDB() SET MULTI_USER FAILED: HRESULT m_hr = 0x80040e14                                              
 , rce\nhpos\plutotal\CnAdoXP.h,   342, IDispatch error #3092                                                                                                   
 , rce\nhpos\plutotal\CnAdoXP.h,   345, [Microsoft][ODBC SQL Server Driver][SQL Server]User does not have permission to alter database 'PluTtlDC', the database 
 , rce\nhpos\plutotal\CnAdoXP.h,   349, database 'PluTtlDC', the database does not exist, or the database is not in a state that allows access checks.          
 ,                 PluTtlDb.cpp,   454, CnPluTotalDb::RestoreDB() SET MULTI_USER FAILED: HRESULT m_hr = 0x80040e14                                              
 , rce\nhpos\plutotal\CnAdoXP.h,   342, IDispatch error #3092                                                                                                   
 , rce\nhpos\plutotal\CnAdoXP.h,   345, [Microsoft][ODBC SQL Server Driver][SQL Server]User does not have permission to alter database 'PluTtlDC', the database 
 , rce\nhpos\plutotal\CnAdoXP.h,   349, database 'PluTtlDC', the database does not exist, or the database is not in a state that allows access checks.          
 ,                 PluTtlDb.cpp,   454, CnPluTotalDb::RestoreDB() SET MULTI_USER FAILED: HRESULT m_hr = 0x80040e14                                              
 , rce\nhpos\plutotal\CnAdoXP.h,   342, IDispatch error #3092                                                                                                   
 , rce\nhpos\plutotal\CnAdoXP.h,   345, [Microsoft][ODBC SQL Server Driver][SQL Server]User does not have permission to alter database 'PluTtlDC', the database 
 , rce\nhpos\plutotal\CnAdoXP.h,   349, database 'PluTtlDC', the database does not exist, or the database is not in a state that allows access checks.          
 ,                 PluTtlDb.cpp,   454, CnPluTotalDb::RestoreDB() SET MULTI_USER FAILED: HRESULT m_hr = 0x80040e14                                              
 , rce\nhpos\plutotal\CnAdoXP.h,   342, IDispatch error #3092                                                                                                   
 , rce\nhpos\plutotal\CnAdoXP.h,   345, [Microsoft][ODBC SQL Server Driver][SQL Server]User does not have permission to alter database 'PluTtlDC', the database 
 , rce\nhpos\plutotal\CnAdoXP.h,   349, database 'PluTtlDC', the database does not exist, or the database is not in a state that allows access checks.          
 ,                 PluTtlDb.cpp,   454, CnPluTotalDb::RestoreDB() SET MULTI_USER FAILED: HRESULT m_hr = 0x80040e14                                              
 ,                   TtlPluBk.c,   190, **WARNING:  TTL_BKUPPLUDBFAIL  PluTotalRestoreDB() returned ulSts 9999 0x270f                                           

Additional Source Code

The OpenRec() method is as follows:

virtual HRESULT OpenRec(CnVariant Source,_bstr_t ActiveConnection,
        CursorTypeEnum CursorType = adOpenKeyset,
        LockTypeEnum LockType = adLockOptimistic,
        long Options = adCmdUnknown)
{
    CString  errorMessage;
    try {
        if (m_pConnection->State != adStateOpen)
        {
            m_pConnection->ConnectionString = ActiveConnection;
            m_pConnection->Open("", "", "", -1);
        }
        m_hr = m_pRecordSet->Open((VARIANT)Source, _variant_t((IDispatch *)m_pConnection),CursorType,LockType,Options);
    }
    catch( _com_error &e) {
        _bstr_t bstrSource(e.Description());
        TCHAR *description;
        description = bstrSource;
        errorMessage.Format(_T("ERROR OpenRec %s  %s"), e.ErrorMessage(), description);
        m_hr = e.Error();
    }           
    if (!FAILED(m_hr)) {
        m_bOpened = TRUE;
    }
    else {
        TRACE3("%S(%d): %s\n", __FILE__, __LINE__, errorMessage);
    }
    return  m_hr;
}

where m_pRecordSet is of type _RecordsetPtr, provided by ADO.