July 31, 2003 at 3:00 pm
I have a stored procedure (appended at the end of this message) that does database backups for all databases in an instance. This procedure uses xp_cmdshell to make sure that the appropriate directories exist for saving the backups. It also uses print statements to say what it is doing. I would like to retrieve all of this output into a VC++ program using ADO. It works correctly if invoked via osql or Query Analyzer. But I cannot get it to work with VC++. Sometimes it does not backup all of the database and I have never been able to get all of the output.
Yes I did try using RAISERROR statements to force the print and backup database output to be added to the error collection of the conection as per the Micosoft Knowledge Base Artical 194792 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;194792).
-----------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dbsp_fullbackup]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dbsp_fullbackup]
GO
/****** Object: Stored Procedure dbo.dbsp_fullbackup Script Date: 5/20/2003 2:51:20 PM ******/
create procedure dbsp_fullbackup
@backuppath varchar(255) = 'C:\mssql\backup',
@password varchar(255) = NULL,
@medianame varchar(255) = NULL,
@mediapassword varchar(255) = NULL,
@mediadescription varchar(255) = NULL,
@debug tinyint = 0
as
/*
*************************************************************
name: dbsp_fullbackup
description:
perform a complete backup of all databases on the server except
the model, tempdb, pubs, and northwind.
usage: exec dbsp_fullbackup 'd:', 'password', 'medianame', 'mediapassword', 'mediadescription', 1
author: steve jones (http://www.sqlservercentral.com/scripts/contributions/90.asp)
input params:
-------------
@backuppath varchar(255), path to backup to.
@password varchar(255), password to be used to lock the backup
@medianame varchar(255), labels the media set
@mediapassword varchar(255), password used to lock the media set
@mediadescription varchar(255), a text description of the entire media set
@debug tinyint, If set to 1, prints commands rather than exec()ing
output params:
--------------
return:
results:
---------
modifications:
--------------
IatroSoft - Fixed to deal with an arbitruary backup path.
- Added password, medianame, mediapassword and mediadescription clause support.
- Removed dbalog.
*************************************************************
*/
set quoted_identifier off
begin
set nocount on
declare
@err int, -- holds error value
@dbname sysname, -- name of the database to backup
@password_clause varchar(255), -- password to be used to lock the backup
@medianame_clause varchar(255), -- labels the media set
@mediapassword_clause varchar(255), -- password used to lock the media set
@mediadescription_clause varchar(255), -- a text description of the entire media set
@cmd varchar(255), -- holds the command string to be executed
@curdate datetime, -- current date/timestamp
@yr varchar(4), -- timestamp year
@mon varchar(2), -- timestamp month
@day varchar(2), -- timestamp day
@hr varchar(2), -- timestamp hour
@min varchar(2), -- timestamp minute
@sec varchar(2), -- timestamp seconds
@tmstmp varchar(14), -- timestamp value
@bulkcopy int, -- flag for bulk copy option being set
@detached int, -- flag for a detached database
@emergency int, -- flag for emergency mode set for suspect DB
@load int, -- flag for database being loaded
@recovery int, -- flag for database being recovered
@offline int, -- flag for database set as offline
@shutdown int, -- flag for database having a problem at startup and being shutdown
@suspect int, -- flag for database begin set as suspect
@trunc int, -- flag for database having the truncate log checkpoint set
@readonly int -- flag database being set as Read-Only
select @err = 0
/*
** check parameters and exit if not correct.
*/
if @backuppath is NULL
select @err = -1
if @err = -1
begin
raiserror( 'parameter error:usage:exec dbsp_fullbackup', 12, 1)
return @err
end
if @password is NOT NULL
select @password_clause = 'password = ''' + @password + ''', '
else
select @password_clause = ''
if @medianame is NOT NULL
select @medianame_clause = 'medianame = ''' + @medianame + ''', '
else
select @medianame_clause = ''
if @mediapassword is NOT NULL
select @mediapassword_clause = 'mediapassword = ''' + @mediapassword + ''', '
else
select @mediapassword_clause = ''
if @mediadescription is NOT NULL
select @mediadescription_clause = 'mediadescription = ''' + @mediadescription + ''', '
else
select @mediadescription_clause = ''
/*
** Compute and set the timestamp for the backup to start. While not exact, this
** section should complete within a minute, so close enough.
*/
select @curdate = getdate()
select @yr = datepart(yyyy, @curdate)
if len(datepart(mm, @curdate)) = 1
select @mon = '0' + cast( datepart(mm, @curdate) as char(1))
else
select @mon = cast( datepart(mm, @curdate) as char(2))
if len(datepart(dd, @curdate)) = 1
select @day = '0' + cast( datepart(dd, @curdate) as char(1))
else
select @day = cast( datepart(dd, @curdate) as char(2))
if len(datepart(hh, @curdate)) = 1
select @hr = '0' + cast( datepart(hh, @curdate) as char(1))
else
select @hr = cast( datepart(hh, @curdate) as char(2))
if len(datepart(mi, @curdate)) = 1
select @min = '0' + cast( datepart(mi, @curdate) as char(1))
else
select @min = cast( datepart(mi, @curdate) as char(2))
if len(datepart(ss, @curdate)) = 1
select @sec = '0' + cast( datepart(ss, @curdate) as char(1))
else
select @sec = cast( datepart(ss, @curdate) as char(2))
select @tmstmp = @yr + @mon + @day + @hr + @min + @sec
/*
** Log the start process
*/
print 'Backups Started: ' + @tmstmp
/*
** If backuppath does not exist, create the directories for backups
*/
declare @len int, @colon int, @path varchar(255), @start int , @l int
select @len = len(@backuppath)
select @colon = charindex(':',@backuppath)
if @colon = 2
select @path = substring(@backuppath, 1, 2), @start = 3
else
select @path = '', @start = 1
while @start < @len
begin
select @l = charindex('\', @backuppath, @start + 1)
if @l = 0
select @l = @len + 1
select @path = @path + substring(@backuppath, @start, @l - @start), @start = @l
select @cmd = 'exec master..xp_cmdshell ''if not exist "' + @path + '" md "' + @path + '"'''
if @debug = 1
print @cmd
else
exec (@cmd)
end
select @backuppath = @path + '\'
/*
** create a cursor with all the user database names
*/
if object_id( '#mydbs') is NOT NULL
drop table #mydbs
create table #mydbs
( dbname char( 50)
, size char( 20)
, dbowner char( 50)
, dbid int
, crdate datetime
, status varchar( 1000)
, lvl char( 4)
)
insert #mydbs Exec sp_helpdb
declare dbnamecursor cursor for
select o.dbname
from #mydbs o
where o.dbname not in (
'master', -- handled after loop
'msdb', -- handled after loop
'northwind', -- demo database - ignored
'pubs', -- demo database - ignored
-- 'model', -- SQL Server's template database for new database - ignored
'tempdb') -- SQL Server's temporary work area - ignored
order by o.dbname
/*
** Open the cursor and begin looping
*/
open dbnamecursor
fetch dbnamecursor into @dbname
while @@fetch_status = 0
begin
print 'Backup Working: ' + rtrim(@dbname) + ' ' + convert(char(24), getdate(), 113)
--create directory for this database
select @cmd = 'exec master..xp_cmdshell ''if not exist "' + @backuppath + rtrim( @dbname) + '" md "' + @backuppath + rtrim( @dbname) + '"'''
if @debug = 1
print @cmd
else
exec (@cmd)
--rename files for tape backup
select @cmd = 'exec master..xp_cmdshell ''ren "' + @backuppath + rtrim( @dbname) + '\*.bak" *.ba1'''
if @debug = 1
print @cmd
else
exec (@cmd)
select @cmd = 'exec master..xp_cmdshell ''ren "' + @backuppath + rtrim( @dbname) + '\*.trn" *.tr1'''
if @debug = 1
print @cmd
else
exec (@cmd)
/*
** set variable options for this database
*/
select @detached = databaseproperty(@dbname, 'isdetached')
select @emergency = databaseproperty(@dbname, 'isemergencymode')
select @load = databaseproperty(@dbname, 'isinload')
select @recovery = databaseproperty(@dbname, 'isinrecovery')
select @offline = databaseproperty(@dbname, 'isoffline')
select @shutdown = databaseproperty(@dbname, 'isshutdown')
select @suspect = databaseproperty(@dbname, 'issuspect')
select @readonly = databaseproperty(@dbname, 'isreadonly')
select @bulkcopy = (databaseproperty(@dbname, 'isbulkcopy'))
select @trunc = (databaseproperty(@dbname, 'istrunclog'))
/******************
-- debugging info
print (@bulk)
print (@dbo)
print (@detached)
print (@emergency)
print (@load)
print (@recovery)
print (@offline)
print (@readonly)
print (@shutdown)
print (@single)
print (@suspect)
print (@trunc)
******************/
if @detached = 0
begin
if @emergency = 0
begin
if @load = 0
begin
if @recovery = 0
begin
if @offline = 0
begin
if @shutdown = 0
begin
if @suspect = 0
begin
if @bulkcopy = 0
begin
if @trunc = 0
begin
print 'Database is not marked truncate on checkpoint.'
select @cmd = 'backup database '
+ quotename(rtrim( @dbname), '')
+ ' to disk = ''' + @backuppath + rtrim( @dbname) + '\' + rtrim( @dbname) + '_' + @tmstmp + '.bak'''
+ ' with ' + @password_clause + @medianame_clause + @mediapassword_clause + @mediadescription_clause + 'init, noformat, noskip'
if @debug = 1
print @cmd
else
exec (@cmd)
--select 'ERROR: ', @@error
if @@error > 0
begin
print 'ERROR: There was an error: ' + @@error
--truncate log
select @cmd = 'backup log '
+ quotename(rtrim( @dbname), '')
+ ' with ' + @password_clause + @medianame_clause + @mediapassword_clause + @mediadescription_clause + 'no_log'
if @debug = 1
print @cmd
else
exec (@cmd)
--do full backup of database
select @cmd = 'backup database '
+ quotename( rtrim( @dbname), '')
+ ' to disk = ''' + @backuppath + rtrim( @dbname) + '\' + rtrim( @dbname) + '_' + @tmstmp + '.bak'''
+ ' with ' + @password_clause + @medianame_clause + @mediapassword_clause + @mediadescription_clause + 'init, noformat, noskip'
if @debug = 1
print @cmd
else
exec (@cmd)
end
end
else
begin
print 'Database is marked truncate on checkpoint.'
--do full backup of database
select @cmd = 'backup database '
+ quotename( rtrim( @dbname), '')
+ ' to disk = ''' + @backuppath + rtrim( @dbname) + '\' + rtrim( @dbname) + '_' + @tmstmp + '.bak'''
+ ' with ' + @password_clause + @medianame_clause + @mediapassword_clause + @mediadescription_clause + 'init, noformat, noskip'
if @debug = 1
print @cmd
else
exec (@cmd)
--change dboption
select @cmd = 'exec sp_dboption ''' + rtrim( @dbname) + ''', ''trunc. log on chkpt.'', ''false'''
if @debug = 1
print @cmd
else
exec (@cmd)
print 'Backups Reset: ' + rtrim( @dbname)
end
end
else
begin
print 'Database is marked for bulk operations.'
--check truncate on checkpoint
if @trunc = 0
begin
--do full backup of database
select @cmd = 'backup database '
+ quotename( rtrim( @dbname), '')
+ ' to disk = ''' + @backuppath + rtrim( @dbname) + '\' + rtrim( @dbname) + '_' + @tmstmp + '.bak'''
+ ' with ' + @password_clause + @medianame_clause + @mediapassword_clause + @mediadescription_clause + 'init, noformat, noskip'
if @debug = 1
print @cmd
else
exec (@cmd)
--change dboption
select @cmd = 'exec sp_dboption ''' + rtrim( @dbname) + ''', ''select into/bulkcopy'', ''false'''
if @debug = 1
print @cmd
else
exec (@cmd)
end
else
begin
--do full backup of database
select @cmd = 'backup database '
+ quotename( rtrim( @dbname), '')
+ ' to disk = ''' + @backuppath + rtrim( @dbname) + '\' + rtrim( @dbname) + '_' + @tmstmp + '.bak'''
+ ' with ' + @password_clause + @medianame_clause + @mediapassword_clause + @mediadescription_clause + 'init, noformat, noskip'
if @debug = 1
print @cmd
else
exec (@cmd)
--change dboptions
select @cmd = 'exec sp_dboption ''' + rtrim( @dbname) + ''', ''select into/bulkcopy'', ''false'''
if @debug = 1
print @cmd
else
exec (@cmd)
select @cmd = 'exec sp_dboption ''' + rtrim( @dbname) + ''', ''trunc. log on chkpt.'', ''false'''
if @debug = 1
print @cmd
else
exec (@cmd)
end
end
end
else
begin
print 'ERROR: Database is suspect and is not available for backup operations.'
end
end
else
begin
print 'ERROR: Database is shutdown and is not available for backup operations.'
end
end
else
begin
print 'ERROR: Database is offline and is not available for backup operations.'
end
end
else
begin
print 'ERROR: Database is in recovery and is not available for backup operations.'
end
end
else
begin
print 'ERROR: Database is marked for load and is not available for backup operations.'
end
end
else
begin
print 'ERROR: Database is in emergency mode and is not available for backup operations.'
end
end
else
begin
print 'ERROR: Database is detached and is not available for backup operations.'
end
print 'Backup Done: ' + rtrim(@dbname) + ' ' + convert(char(24), getdate(), 113)
--get next user db
fetch dbnamecursor into @dbname
end
close dbnamecursor
deallocate dbnamecursor
drop table #mydbs
--backup master and msdb
print 'Backup Working: master & msdb ' + convert(char(24), getdate(), 113)
--create directory for databases if they don't exist
select @cmd = 'exec master..xp_cmdshell ''if not exist "' + @backuppath + 'master" md "' + @backuppath + '\master"'''
if @debug = 1
print @cmd
else
exec (@cmd)
select @cmd = 'exec master..xp_cmdshell ''if not exist "' + @backuppath + 'msdb" md "' + @backuppath + '\msdb"'''
if @debug = 1
print @cmd
else
exec (@cmd)
--rename files for tape backup
select @cmd = 'exec master..xp_cmdshell ''ren "' + @backuppath + 'msdb\*.bak" *.ba1'''
if @debug = 1
print @cmd
else
exec (@cmd)
select @cmd = 'exec master..xp_cmdshell ''ren "' + @backuppath + 'master\*.bak" *.ba1'''
if @debug = 1
print @cmd
else
exec (@cmd)
--do backup master
select @cmd = 'backup database master'
+ ' to disk = ''' + @backuppath + 'master\master_' + @tmstmp + '.bak'''
+ ' with ' + @password_clause + @medianame_clause + @mediapassword_clause + @mediadescription_clause + 'init, noformat, noskip'
if @debug = 1
print @cmd
else
exec (@cmd)
--do backup msdb
select @cmd = 'backup database msdb'
+ ' to disk = ''' + @backuppath + 'msdb\msdb_' + @tmstmp + '.bak'''
+ ' with ' + @password_clause + @medianame_clause + @mediapassword_clause + @mediadescription_clause + 'init, noformat, noskip'
if @debug = 1
print @cmd
else
exec (@cmd)
print 'Backup Done: master & msdb ' + convert(char(24), getdate(), 113)
end
return @err
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
August 1, 2003 at 3:59 am
Can you post you C++ code so I have an idea what you have done so far? Thanks
August 4, 2003 at 11:40 am
Here's the code fragment that I'm currently dealing with...
I've also appended the VB example that I've been playing with from the MSDN Knowledge Base Articale 194782.
static int ConnectionExecuteSP(AdoConnection& pCon, CString csDbCmd,
long& lRecordsAffected,
AdoRecordset& pRst,
bool bLog)
{
_variant_t vRecordsAffected(0L);
try
{
_bstr_t bsDbCmd = csDbCmd;
AdoCommand pCmd;
//_variant_t vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);
long lErr;
TESTHR(pCmd.CreateInstance(__uuidof(ADODB::Command)));
pCmd->ActiveConnection = pCon;
pCmd->CommandText = "dbsp_fullbackup";
pCmd->CommandType = ADODB::adCmdStoredProc;
pCmd->Parameters->Append(
pCmd->CreateParameter("@err", ADODB::adInteger, ADODB::adParamReturnValue, sizeof(int), _variant_t("0")) );
pCmd->Parameters->Append(
pCmd->CreateParameter("@backuppath", ADODB::adVarChar, ADODB::adParamInput, 10, "C:\\backups") );
//pRst = pCmd->Execute(&vtEmpty, &vtEmpty, ADODB::adCmdUnknown);
pRst = pCmd->Execute(NULL, NULL, ADODB::adCmdStoredProc);
//pRst = pCon->Execute(bsDbCmd, &vRecordsAffected, ADODB::adCmdText);
}
catch(_com_error &e)
{
CString csError;
_bstr_t bstrDescription(e.Description());
if (bLog)
{
LogEvent(DB_ERR, "%08lx:%s. %s~%s", e.Error(), e.ErrorMessage(),
(LPCSTR)bstrDescription, (LPCSTR)csDbCmd);
}
return 1;
}
lRecordsAffected = vRecordsAffected.lVal;
return 0;
}
/*
*
*
*/
void ReplBackupServer(CString& csBackupCmd)
{
enum RepServerConnectionType eConnectType;
AdoConnection pCon;
CString csPrimaryDb = RepGetConnectString(eConnectType);
if (!csPrimaryDb.IsEmpty() && CreateDataSrcConnection(pCon, csPrimaryDb)) {
long lRecordsAffected = 0;
AdoRecordset r;
//pCon->Errors->Clear();
//pCon->CursorLocation = ADODB::adUseServer;
//TESTHR(r.CreateInstance(__uuidof(ADODB::Recordset)));
if (ConnectionExecuteSP(pCon, csBackupCmd, lRecordsAffected, r, true) == 0) {
//if (OpenDbSpQuery(pCon, r, csBackupCmd) == 0) {
//AdoConnection rCon;
ADODB::ErrorPtr pErr = NULL;
long lCount;
_variant_t vTmp;
_bstr_t bsTmp, bsTmpDesc, bsTmpSrc, bsTmpState, bsTmpNatErr;
char *pcTmp, *pcTmpDesc, *pcTmpSrc, *pcTmpState, *pcTmpNatErr;
char buf[4096];
long rs = 0, m;
while (r != NULL) {
sprintf(buf, "RS%02d ***START***", rs);
LogEvent(LOG_NO_ERROR, buf);
m = 0;
while (!r->EndOfFile) {
vTmp = r->Fields->Item[0L]->Value;
if (vTmp.vt != VT_NULL) {
bsTmp = vTmp;
pcTmp = bsTmp;
sprintf(buf, "RS%02dM%02d: %s", rs, m, pcTmp);
LogEvent(LOG_NO_ERROR, buf);
//LogEvent(LOG_NO_ERROR, pcTmp);
}
else {
sprintf(buf, "RS%02dM%02d: ***NULL***", rs, m);
LogEvent(LOG_NO_ERROR, buf);
}
r->MoveNext();
m++;
}
//rCon = r->GetActiveConnection();
lCount = pCon->Errors->Count;
for (long i = 0; i < lCount; i++) {
pErr = pCon->Errors->GetItem(i);
bsTmpDesc = pErr->Description;
pcTmpDesc = bsTmpDesc;
bsTmpSrc = pErr->Source;
pcTmpSrc = bsTmpSrc;
bsTmpState = pErr->SQLState;
pcTmpState = bsTmpState;
bsTmpNatErr = pErr->NativeError;
pcTmpNatErr = bsTmpNatErr;
sprintf(buf, "RS%02dE%02d: %s; %s; %s; %s", rs, i, pcTmpDesc, pcTmpSrc, pcTmpState, pcTmpNatErr);
LogEvent(LOG_NO_ERROR, buf);
//LogEvent(LOG_NO_ERROR, pErr->Description);
}
//pCon->Errors->Clear();
lRecordsAffected = 0;
r = r->NextRecordset((VARIANT *)lRecordsAffected);
rs++;
}
//r->Close(); // NextRecordset() does the Close().
}
pCon->Close();
}
}
Sub CreateParms()
Dim ADOCmd As New ADODB.Command
Dim ADOPrm As New ADODB.Parameter
Dim ADOCon As ADODB.Connection
Dim ADORs As ADODB.Recordset
Dim sParmName As String
Dim strConnect As String
Dim rStr As String
On Error GoTo ErrHandler
strConnect = "driver={SQL Server};server=DEVTEST-A14498F\EPATIENT;database=ePatient"
Set ADOCon = New ADODB.Connection
With ADOCon
.Provider = "MSDASQL"
.CursorLocation = adUseServer 'Must use Server side cursor.
.ConnectionString = strConnect
.Open
End With
Set ADOCmd.ActiveConnection = ADOCon
With ADOCmd
.CommandType = adCmdStoredProc
' .CommandText = "ADOTestRPE"
.CommandText = "dbsp_fullbackup"
End With
'Parameter 0 is the stored procedure Return code.
sParmName = "Return"
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adInteger, _
adParamReturnValue, , 0)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = -1
sParmName = "backuppath"
Set ADOPrm = ADOCmd.CreateParameter(sParmName, adVarChar, _
adParamInput)
ADOCmd.Parameters.Append ADOPrm
ADOCmd.Parameters(sParmName).Value = "C:\backups"
Set ADORs = ADOCmd.Execute
Do While (Not ADORs Is Nothing)
If ADORs.State = adStateClosed Then Exit Do
While Not ADORs.EOF
For i = 0 To ADORs.Fields.Count - 1
rStr = rStr & " : " & ADORs(i)
Next i
Debug.Print Mid(rStr, 3, Len(rStr))
ADORs.MoveNext
rStr = ""
Wend
Debug.Print "----------------------"
Set ADORs = ADORs.NextRecordset
Loop
Debug.Print "Return: " & ADOCmd.Parameters("Return").Value
Debug.Print "Output: " & ADOCmd.Parameters("Output").Value
GoTo Shutdown
ErrHandler:
Call ErrHandler(ADOCon)
Resume Next
Shutdown:
Set ADOCmd = Nothing
Set ADOPrm = Nothing
Set ADORs = Nothing
Set ADOCon = Nothing
End Sub
Private Sub Command1_Click()
Call CreateParms
End Sub
Sub ErrHandler(objCon As Object)
Dim ADOErr As ADODB.Error
Dim strError As String
For Each ADOErr In objCon.Errors
strError = "Error #" & ADOErr.Number & vbCrLf & ADOErr.Description _
& vbCr & _
" (Source: " & ADOErr.Source & ")" & vbCr & _
" (SQL State: " & ADOErr.SQLState & ")" & vbCr & _
" (NativeError: " & ADOErr.NativeError & ")" & vbCr
If ADOErr.HelpFile = "" Then
strError = strError & " No Help file available" & vbCr & vbCr
Else
strError = strError & " (HelpFile: " & ADOErr.HelpFile & ")" _
& vbCr & _
" (HelpContext: " & ADOErr.HelpContext & ")" & _
vbCr & vbCr
End If
Debug.Print strError
Next
objCon.Errors.Clear
End Sub
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply