Library tutorials & articles
Using SQL-DMO To Backup and Verify a Database
- Overview
- Getting Started
- Backing Up
- Verifying the backup
Verifying the backup
If we were using Visual Basic or C++ we could use events to check the progress of the backup; however, this is not possible with ASP. We will use the SQLDMO.BackupDevice object's ReadBackupHeader method to confirm the backup. The code below code is from verify.asp, its takes the name of a backup device and provides information on the latest backup.
<%@ Language=VBScript %>
<
HTML
>
<
BODY
>
<!--Login information -->
<!--#include file=login.asp-->
<P><%Dim objDevice
Dim objResults
Dim iCount
Dim xCount
'Create the backup device objectSet objDevice = Server.CreateObject("SQLDMO.BackupDevice")
'Loop through the devices until we find a matchFor Each objDevice In srv.BackupDevices
If objDevice.Name = Request("fname") Then'We found a match now read the resultsSet objResults = objDevice.ReadBackupHeader
For iCount = 1 To objResults.Rows
For xCount = 1 To objResults.Columns%><B><%=objResults.ColumnName(xcount)%><B>: <%=objResults.GetColumnString(icount,xcount)%><br><%Next%><HR><%Next%><%End If%><%Next%><%
srv.Disconnect
set srv = nothingset objDevice = nothingset objResults = nothing%></BODY></HTML>
The ReadBackupHeader method returns a QueryResults object. I use the Rows property of this object to see how many rows are returned. I then loop through the rows and columns to retrieve the information.
How Can I Use This Stuff
I find it useful for executing remote backups and restores. We have not covered restores yet, but SQL-DMO gives you this power as well.
Summary
I hope that this article has provided you with some insight into the power of SQL-DMO. As always if you have any questions, comments, or just want to stop by and buy me some coffee of your next trip to Seattle, I can be reached at cwilson@harborviewsolutions.com.
Related articles
Related discussion
-
High-Performance .NET Application Development & Architecture
by Manjot Bawa (0 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)
-
Job Opening - New Braunfels, Tx for C# Programmer
by kirkkelly (3 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...
Events coming up
-
Nov
19
SQLBits V
Newport, United Kingdom
SQLBits is Europe's largest SQL Server conference, and SQLBits V will be the biggest and best yet. On November 19th we are holding a day of pre-conference seminars; on November 20th we have a pay-to-attend day of SQL Server 2008 and R2 content; and on Saturday November 21st we have our usual free community conference.
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.