Library tutorials & articles
Using SQL-DMO To Backup and Verify a Database
Overview
The SQL Distributed Management objects (SQL-DMO) extend the functionality of SQL Server by providing developers a way to execute common tasks using programming and scripting languages. The first article in this series entitled "Using the SQL Distributed Management Objects", provided an overview of SQL-DMO. This article expands upon those concepts by showing you how to use SQL-DMO to create an Active Server Page application to backup and verify a SQL Server database.
What You Will Need
You will need knowledge of how to backup a SQL Server database. Additionally make a reference to the SQL-DMO library in your global.asa file. The below snippet is a reference for SQL Server 2000.
<!--METADATA TYPE="TypeLib" NAME="Microsoft SQLDMO
Object Library" UUID="{10010001-E260-11CF-AE68-00AA004A34D5}"
VERSION="8.0"-->
The sample application and code snippets will work for SQL 7.0, MSDE, and SQL Server 2000.
Joy and Pain
Working with the SQL-DMO object can be both a joy and pain. It provides you with so much rich functionality it often becomes confusing trying to figure out what properties and methods you really need. On that note, I'm only going to discuss the objects, properties, and methods used within the sample application. You can find unlimited information and code samples in the SQL Server books on-line. You can also download the sample application that accompanies this article. It provides ASP code for all topics covered below.
Related articles
Related discussion
-
High-Performance .NET Application Development & Architecture
by Manjot Bawa (0 replies)
-
VS.NET/sql server installation problem
by daspeac (4 replies)
-
Research topic in software
by reachsangeethamathew (0 replies)
-
Error Msg Description ?
by morizan (0 replies)
-
How to Change Default exe Icon in C#.net Windows Application
by sonali.terse (2 replies)
Related podcasts
-
Stack Overflow: Podcast #28
This is the twenty-eighth episode of the StackOverflow podcast, where Joel and Jeff discuss Windows Azure, SQL Server 2008 full text search, Bayesian filtering, porn detection, and project management — among other things. Jeff met the inestimable Joey DeVilla aka Accordion Guy...
here the solution for Backup the database in VC++:
int backup::OnButton1()
{
HRESULT hr;
if FAILED(hr = CoInitialize (NULL))
{
//_tprintf(TEXT("CoInitialize Failed\n"));
return (0);
}
LPSQLDMOSERVER pSQLServer = NULL;
if FAILED(hr = CoCreateInstance(
CLSIDSQLDMOServer,
NULL,
CLSCTXINPROCSERVER,
IIDISQLDMOServer,
(LPVOID*)&pSQLServer))
{
//_tprintf(TEXT("CoCreateInstance Failed\n"));
return (0);
}
pSQLServer->SetLoginTimeout(10);
pSQLServer->SetLoginSecure(TRUE);
if FAILED(hr = pSQLServer->Connect((L"ws-45"),(L"sa"),(L"")))
{
AfxMessageBox("Backup connection Failed");
return DisplayError();
}
else
{
LPSQLDMODATABASES pDatabases = NULL;
LPSQLDMODATABASE pDatabase = NULL;
LPSQLDMOFILEGROUPS pFileGroups = NULL;
LPSQLDMOFILEGROUP pFileGroup = NULL;
LONG lCount = 0;
LPSQLDMODBFILE ppDBFile = NULL;
LPLONG pRetVal = NULL;
long mdatasize;
CString temp;
float mdatasizeMB;
CString datasize;
SQLDMOBSTR strDB;
// long *strDB;
// char* mstr = new char[100];
//pSQLServer->GetDatabaseCount(&lCount);
hr = pSQLServer->GetDatabases(&pDatabases);
for (LONG i = 0; i < 1; i++)
{
pDatabases->GetItemByOrd(i, &pDatabase);
pDatabase->GetName(&strDB);
pDatabase->Release();
pDatabase->GetSpaceAvailableInMB(&m_datasizeMB);
pDatabase->GetFileGroups(&pFileGroups);
pDatabase->GetSize(&m_datasize);
// pDatabase->GetFileGroupByName(_T("DOTS"),&pFileGroup);
// pFileGroup->GetDBFileByOrd(0,&ppDBFile);
// temp = mdatasize;
datasize.Format("%d",mdatasize);
temp.Format("%d",mdatasizeMB);
SetDlgItemText(IDCEDIT1,datasize);
//AfxMessageBox(datasize);
//AfxMessageBox(temp);
// sprintf(mstr,"%lf",msize);
// wprintf(T("database %s"), strDB);
// wprintf(T(" Total size %i \n"), msize);
// wprintf(T("Free space %lf \n"), m_sizeMB);
}
}
LPSQLDMOBACKUP pSQLBackup = NULL;
if FAILED(hr = CoCreateInstance(
CLSIDSQLDMOBackup,
NULL,
CLSCTXINPROCSERVER,
IIDISQLDMOBackup,
(LPVOID*)&pSQLBackup))
{
// _tprintf(TEXT("CoCreateInstance Failed\n"));
return (0);
}
LPCONNECTIONPOINTCONTAINER pMyConnectionPointContainer;
LPCONNECTIONPOINT pMyConnectionPoint;
CMyBackupSink* pMyBackupSink = new CMyBackupSink();
pMyBackupSink->AddRef();
if (!pMyBackupSink)
{
return(0);
}
DWORD dwCookie;
// ask the connectable object (pSQLBackup) about its outgoing interface,
// by using QI on IIDIConnectionPointContainer. If fails, this object
// does not support outgoing interface
//
if FAILED(pSQLBackup->QueryInterface(
IIDIConnectionPointContainer,
(LPVOID FAR*) &pMyConnectionPointContainer))
{
return DisplayError();
}
// find the specific outgoing interface IID_ISQLDMOBackupSink and retrieve
// a pointer to the connectionpoint object. If fails, the object does not
// support this outgoing interface
if FAILED(pMyConnectionPointContainer->FindConnectionPoint(
IID_ISQLDMOBackupSink,
(LPCONNECTIONPOINT FAR*)&pMyConnectionPoint))
{
return DisplayError();
}
// establish the connection between the Sink and the ConnectionPoint object.
// Retrieve a key (cookie) value for terminating the connection later. If
// this fails, the Sink and ConnectionPoint object do not support the same interface
if (S_OK != (hr = pMyConnectionPoint->Advise((LPUNKNOWN)pMyBackupSink, &dwCookie)))
{
return DisplayError();
}
if FAILED(hr = pSQLBackup->SetFiles((L"e:\test123.dmp") ))
{
return DisplayError();
}
if FAILED(hr = pSQLBackup->SetDatabase((L"dots")))
{
return DisplayError();
}
// hr = pSQLBackup->EXEC sp_spaceused ;
if FAILED(hr = pSQLBackup->SetTruncateLog(SQLDMOBackupLogTruncateOnly))
{
return DisplayError();
}
Hi
I tried your code, seems it is not working for remote database backup.
do u have any update version for taking backups.
thanks
This thread is for discussions of Using SQL-DMO To Backup and Verify a Database.