Monday, March 19, 2012

DMO restore problem

I have a C++ app that is using SQLDMO with SqlServer2000. I am Restoring a database using only the .bak file, moving the .mdf and .ldf files. The following is the code:

#ifdef USE_SQLDMO
try
{
if(m_cpServer == NULL)
return E_FAIL;

_RestorePtr cpRestore;
CheckError(cpRestore.CreateInstance(_T("SQLDMO.Restore")));
cpRestore->Action = SQLDMORestore_Database;
cpRestore->Database = _bstr_t(sDbName);
cpRestore->Files = _bstr_t(sBUFile);
cpRestore->LastRestore = false;
cpRestore->ReplaceDatabase = true;
cpRestore->PercentCompleteNotification= 10;
RestoreSinkPtr cpSinkPtr;
//get data path for current server
GetSQLDataPath(sDataPath);
QueryResultsPtr cpFileList;
cpFileList = cpRestore->ReadFileList( m_cpServer);
//get info to change for move from cpFileList and move items
for(int i = 1; i <= cpFileList->Rows; i++)
{
_bstr_t bstrLogicalName = cpFileList->GetColumnString(i,1);
sLogicalName = (LPCTSTR)bstrLogicalName;
sLogicalName.Trim();
_bstr_t bstrPhysicalName = cpFileList->GetColumnString(i,2);
sPhysicalName = (LPCTSTR)bstrPhysicalName;
sPhysicalName.MakeUpper();
if(sPhysicalName.Find(".MDF") > 0)
{
sPhysicalName = sDataPath + "\\" + sDbName;
sFiles.Format("%i",nDatafiles);
if(nDatafiles == 0)
sPhysicalName += ".mdf";
else
sPhysicalName += "_" + sFiles + ".mdf";

nDatafiles++;
}
else if(sPhysicalName.Find(".LDF") > 0)
{
sFiles.Format("%i",nLogFiles);
sPhysicalName = sDataPath + "\\" + sDbName;
if(nLogFiles == 0)
sPhysicalName += ".ldf";
else
sPhysicalName += "_" + sFiles + ".ldf";

nLogFiles++;
}
sNew += "[" + sLogicalName + "],[" + sPhysicalName + "],";
}
sNew = sNew.Left(sNew.GetLength()-1);
sPhysicalName = (LPCTSTR)bstrPhysicalName;

cpRestore->SQLRestore( m_cpServer);
cpRestore->SQLVerify(m_cpServer);
cpRestore = NULL;
bSuccess = true;
}
catch(_com_error& err)
{
PrintComError(err);
PrintProviderError(m_cpServer);
hr = err.Error();
}
#endif

The problem is that the functions appears to work. The Restore command does not return and error and neither does the verify. When I open enterprise manager, the database created but is greyed out and shows a status of loading but never completes.
Any ideas/help would be greatly appreciated.

Sandy

I haven't tried your script, but I suspect that the LastRestore=false is the culprit, as this indicates that this is not the last backup on the restore chain and keeps the database in a loading state as it is expecting a final log backup will be applied.

No comments:

Post a Comment