Capture results of a SQL Backup Command

[Jeff]

Below is my simple script to backup a database, and the it seems to work fine. My question is....how can I capture the output as if I executed it from SQL Managment Studio? Or at least verify that it has completed successfully. I don't want to verify the physical file is sitting in the backup location with a fileexist command either as the command may be ran from a different server than the SQL server.


SQLDBServer = "MyServerName"
SQLDB = "MyDB"
SQLUser = "SA"
SQLPassword = "MyPassword"



TimeStamp = StrReplace (TimeYmdHms(),"/","-" )
TimeStamp = StrReplace (TimeStamp,":","-" )
TimeStamp = StrUpper(TimeStamp)


mySQLConn = CreateObject('ADODB.Connection')
mySQLCMD = createobject("ADODB.Command")
mySQLRS = CreateObject('ADODB.Recordset')
mySQLRS.CursorLocation = 3

mySQLConn.ConnectionString = "Provider=SQLOLEDB; Data Source=%SQLDBServer%;Password=%SQLPassword%;User ID=%SQLUser%;Initial Catalog=Master;"

adOpenStatic = 1
adLockReadOnly = 4
adCmdText = 1

mySQLConn.Open()

SQLText = StrCat("BACKUP DATABASE [%SQLDB%] TO  DISK = '%SQLDB% %TimeStamp%.bak' WITH NOFORMAT, INIT,  NAME = N'%SQLDB%-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10")

mySQLCMD.activeconnection = mySQLConn

mySQLCMD.commandtext = SQLText
mySQLRS = mySQLCMD.execute



;mySQLRS.Close
mySQLConn.Close()
mySQLRS = 0
mySQLConn = 0

[Deana]

Often you can query the result set to confirm you got the data. however I am not sure if the backup Database SQL command returns anything. I wonder if you can obtain the results you are looking for, using the ADO Error object?

The following threads seems to indicate that you can: http://stackoverflow.com/questions/11908741/how-do-i-get-the-result-output-from-an-sql-backup-command-into-a-delphi-program

The tech database has a few examples using ADODB.Error:
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+Tutorials/OLE~TUTORIAL+WINBATCH~OLE~PROGRAMMING~-~Part~1.txt
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/Errors+OLE~Error~Trapping.txt

[Jeff]

Are you thinking somthing like?

ErrorMode(@OFF)
mySQLRS = mySQLCMD.execute("RA,0,Integer(adCmdText)")
ErrorMode(@CANCEL)

while (mySQLRS <> 0)

>>>do somthing?

endwhile

[Deana]

No I was thinking something more like this:

Code (winbatch) Select Expand

mySQLError = CreateObject('ADODB.Error')

mySQLRS = mySQLCMD.execute

If mySQLError.Count > 0
   Pause("Notice",mySQLError.Number,@CRLF,mySQLError.Description)
Endif