Hello all,
I am writing a utility to backup a SQL database(s) prior to the upgrade of an application. One of the things that is bugging me, there doesn't seem to be a simple way to monitor the progress of the backup or capture the results of the back up. Using SQL management studio, you can execute the backup command with the "STATS" parameter and see the progress in the messages tab. Is there a way to capture these results as they occur?
Below is a sample of what I am playing around with, so excuse some of the sloppy syntax.
#DefineSubroutine SQLDBBACKUP()
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 = 'PreUpgrade_%SQLDB%_%TimeStamp%.bak' WITH NOFORMAT, INIT, NAME = N'%SQLDB%-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10")
mySQLCMD.activeconnection = mySQLConn
mySQLCMD.commandtext = SQLText
; ErrorMode(@OFF)
mySQLRS = mySQLCMD.execute;("RA,0,Integer(adCmdText)")
; ErrorMode(@CANCEL)
mySQLConn.Close()
mySQLRS = 0
mySQLConn = 0
handle = FileOpen(LOGFILE, "Append")
FileWrite(handle, StrCat(TimeYmdHms (), @TAB, "End - SQLDBBACKUP() "))
FileClose(handle)
#EndFunction