Capture progress or results of a SQL database backup

[Jeff]

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

[td]

Don't know if this will get you any closer to your goal but I suppose you could run the sqlcmd utility with a  WinBatch run or ShellExecute functions and see what happens.

Perhaps someone with more expertise in this area will offer a solution that is more likely to succeed.

[td]

The "ADODB.Connection" object does have an event interface and WinBatch can consume COM Automation events but I have no idea if MSFT's SQLODBC drive make any use of COM Automation events in your case.

[Jeff]

I changed the following line and got a WinBatch 1261 error message. See the attached.

mySQLRS = mySQLCMD.execute("RA,0,Integer(commandtext)")

When I clicked on the "More Error Info" button, the Com/CLR Exception had "10 percent processed". This would be the first sql message returned if I had ran it in SQL Management Studio during the execution of the backup process. So I know it's being feed back to WinBatch.

Is there a way to gracefully capture the output and feed it into a progress bar? Or something to that affect.

[td]

You can always trap errors using ErrorMode or IntControl 73 but 1261 is an error, not just a progress update.  That means the work being performed has been terminated prematurely.  It would be very unusual to send a progress update via a mechanism used for error reporting.   In fact, it could be considered a bug in the provider.     

[Jeff]

ok, Thanks for the insight. I am not a programmer by trade..

Could you provide me a sample of the "while" loop that would to step through this? The RSMoveNext does not seem to work. When I set the stats to 10, that means it should only loop 10 times.

Thanks!

[td]

It's not the first time it has happened but I apparently I did not do a particularly good job of explaining this.  I can't provide you with a "sample" because based on the information provided there isn't a way to do what you intend.

[Jeff]

ok, I took it like you could progress through the notifications. They are still considered errors but still process through them.

Thanks, You are always a good admin.

[td]

For what it's work, here's a six liner that backs up an MSSQL database.  Of course, it doesn't have your status messages but it show that the backup process can be made to work using WIL COM Automation.

Code: Winbatch

strConnS= "Driver={SQL Server Native Client 11.0};Server=Intrepid\SQLEXPRESS;Database=TestBin;Trusted_Connection=yes;"
objConn=CreateObject("ADODB.Connection")
objConn.Open(strConnS)

; Backup the handy-dandy TestBin database.
strSql = "BACKUP DATABASE [TestBin] TO  DISK = 'c:\temp\bob.bak' WITH NOFORMAT, INIT,  NAME = N'TestBin-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"
objConn.Execute(strSql, , 129)  
objConn.Close()
 

[Jeff]

Thanks for your help! I did end up trapping the results and writing it to a log.