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
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
Are you thinking somthing like?
ErrorMode(@OFF)
mySQLRS = mySQLCMD.execute("RA,0,Integer(adCmdText)")
ErrorMode(@CANCEL)
while (mySQLRS <> 0)
>>>do somthing?
endwhile
No I was thinking something more like this:
mySQLError = CreateObject('ADODB.Error')
mySQLRS = mySQLCMD.execute
If mySQLError.Count > 0
Pause("Notice",mySQLError.Number,@CRLF,mySQLError.Description)
Endif