Is there a way to show backup % done progess?

Question

Post reply

Is there a way to show backup % done progess?

Peter D-251663

Ten Centuries

Points: 1010
More actions
January 2, 2006 at 10:18 am

#110511

When running the statement BACKUP DATABASE MyNwind TO MyNwind_2
is there any other syntaxs available that will give the output (percentage complete) to this task?

Appreciate replies.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

There is an option STATS that defaults to 10%.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Raghu Pyapili SSC Enthusiast Points: 119 More actions · Answer 2

Hi,

Peter, I can help you with the syntax.'Test' is my database and use the STATS option while backing up

backup database test

to disk = N'd:\test\testbackup.bak'

with NAME= N'testbackup.bak',stats=10

The Result would be something like this:

99 percent backed up.

Processed 80 pages for database 'test', file 'test' on file 3.

100 percent backed up.

Processed 1 pages for database 'test', file 'test_log' on file 3.

BACKUP DATABASE successfully processed 81 pages in 0.242 seconds (2.712 MB/sec).

My database is too small to be counted.Hope this helps you.

Peter D-251663 Ten Centuries Points: 1010 More actions · Answer 3

Great thnxs for your help! Its worked out perfectly

peterus SSC Veteran Points: 287 More actions · Answer 4

Try DBCC OUTPUTBUFFER (spid) . Where spid is the backup process. This is as or more accurate than the stats option.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 5

Do you know the command to cause the results to show up in Query Analyzer as they happen instead of when the batch is completed?

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Steve Harrold SSC Enthusiast Points: 114 More actions · Answer 6

I'm not positive, but I believe that in QA if you go into Tools-Options-Results and select 'Scroll results as received', the output will get displayed as it's returned. (It may only work with text output.)

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 7

Thanks! I knew it would be somthing obvious that I overlooked!

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

mohan.bndr SSCrazy Points: 2360 More actions · Answer 8

I see the DBCC inputbuffer (spid) will give what are the current statement is running...but don't about DBCC outputbuffer(spid).

Can you pls describe about DBCC OUTPUTBUFFER and give some more clarification on different between INPUTBUFFER and OUTPUTBIFFER....

Thanks in advance 🙂

mohan.bndr SSCrazy Points: 2360 More actions · Answer 9

peterus (1/3/2006)
Try DBCC OUTPUTBUFFER (spid) . Where spid is the backup process. This is as or more accurate than the stats option.

I see the DBCC inputbuffer (spid) will give what are the current statement is running...but don't about DBCC outputbuffer(spid).

Can you pls describe about DBCC OUTPUTBUFFER and give some more clarification on different between INPUTBUFFER and OUTPUTBIFFER....

Thanks in advance 🙂

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 10

mohan.bndr (4/13/2015)
peterus (1/3/2006)
Try DBCC OUTPUTBUFFER (spid) . Where spid is the backup process. This is as or more accurate than the stats option.
I see the DBCC inputbuffer (spid) will give what are the current statement is running...but don't about DBCC outputbuffer(spid).
Can you pls describe about DBCC OUTPUTBUFFER and give some more clarification on different between INPUTBUFFER and OUTPUTBIFFER....
Thanks in advance 🙂

please check the below link

http://www.sqlservercentral.com/Forums/Topic1156526-1292-1.aspx

peterus SSC Veteran Points: 287 More actions · Answer 11

Wow, a 9 year old post reply!

I use something like this now:

http://stackoverflow.com/questions/152447/is-there-a-sql-script-that-i-can-use-to-determine-the-progress-of-a-sql-server-b

Here's the link to outputbuffer: https://msdn.microsoft.com/en-us/library/ms189821.aspx

maxime.daniou Valued Member Points: 67 More actions · Answer 12

EDIT : I have just figured out the post is very old and is for SQL 7 , 2000 🙂

Hi Peter,

Below the query I use for this purpose:

SELECT percent_complete, start_time,

DATEADD(MILLISECOND, (estimated_completion_time + total_elapsed_time), start_time) AS estimate_end_time,

SUBSTRING(T.text, 1 + CHARINDEX('[', T.text), CHARINDEX(']', T.text) - 1 - CHARINDEX('[', T.text)) AS DatabaseName,

T.text

FROM sys.dm_exec_requests R

CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T

WHERE session_id > 50

AND command LIKE '%back%' OR command LIKE '%rest%';

Hope it will help,

Max.