SQL server backup progress monitoring

I'm trying to provide a progress bar when backing up a database in SQL2000.  I know there is a STATS option under BACKUP command.  But how do I catch any message or event during this type of long query to give user feedback...  I'm using VB for the UI and the progressBar is the activeX component.
yithan020Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arbertCommented:
So how are you actually executing the backup?  ADO and a open connection?
0
yithan020Author Commented:
The backup is done through ADO, I used ADODB.Command, send the SQL text through an ODBC connection, which connects to the SQL.
0
arbertCommented:
SQL Server won't return any information to your ADO connection until the backup completes, so the use of STATS won't return anything until the end (assuming you're using a recordset to capture the output).

Brett
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

yithan020Author Commented:
Yes, okay.  But what I'm wondering, is if there is anyway SQL server does anything during these long queries.  Namely, sets any events or are there any messages being generated, this only needs to work on Windows.  What kind of information does STATS generate, and return to who..., I can try to catch these messages at a lower level.
0
arbertCommented:
At this time, this is an operation that executes and doesn't return any information to the client.  If you want to be able to add a "guage" to the backup process, you would have to use the backup API and create a program (outside of VB) to use it.
0
arbertCommented:
I take that back, you could use SQLDMO from visual basic and query the status of the backup:

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/9_dmor85_6.htm


Brett
0
yithan020Author Commented:
I'm fairly happy with the last comment.  If you could elaborate a little more with like an example, you get the points!  I'm not an expert on WMI, although I don't think this involves that.
0
arbertCommented:
Here's a small example, but I think it will give you the push in the direction you need:


http://66.102.7.104/search?q=cache:b9mg-PFbsr8J:www.588188.com/netbook/sqlserver2000/sqldmo/dmoref_con04_3n3m.htm+PercentComplete+Event+sqldmo+backup+visual+basic&hl=en


SQLDMO doesn't use WMI.  If you go to the project menu, references, you can add a reference to SQLDMO and then the objects will be available to you in sql server.

Brett
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.