Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL server backup progress monitoring

Posted on 2004-11-13
8
Medium Priority
?
653 Views
Last Modified: 2011-10-03
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.
0
Comment
Question by:yithan020
  • 5
  • 3
8 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 12577035
So how are you actually executing the backup?  ADO and a open connection?
0
 

Author Comment

by:yithan020
ID: 12585029
The backup is done through ADO, I used ADODB.Command, send the SQL text through an ODBC connection, which connects to the SQL.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12585947
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:yithan020
ID: 12593072
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
 
LVL 34

Expert Comment

by:arbert
ID: 12606792
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
 
LVL 34

Expert Comment

by:arbert
ID: 12607026
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
 

Author Comment

by:yithan020
ID: 12608457
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
 
LVL 34

Accepted Solution

by:
arbert earned 1000 total points
ID: 12609081
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question