viewpoint-particle

Author Topic: Capture results of a SQL Backup Command  (Read 3551 times)

Jeff

  • Newbie
  • *
  • Posts: 45
Capture results of a SQL Backup Command
« on: January 11, 2014, 08:49:57 pm »
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
Jeff

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: Capture results of a SQL Backup Command
« Reply #1 on: January 13, 2014, 08:23:25 am »
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
Deana F.
Technical Support
Wilson WindowWare Inc.

Jeff

  • Newbie
  • *
  • Posts: 45
Re: Capture results of a SQL Backup Command
« Reply #2 on: January 13, 2014, 10:57:43 am »
Are you thinking somthing like?

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

while (mySQLRS <> 0)

>>>do somthing?
 
endwhile
Jeff

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: Capture results of a SQL Backup Command
« Reply #3 on: January 13, 2014, 11:51:16 am »
No I was thinking something more like this:

Code: Winbatch
mySQLError = CreateObject('ADODB.Error')

mySQLRS = mySQLCMD.execute

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

Deana F.
Technical Support
Wilson WindowWare Inc.