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 14 posts - 1 through 13 (of 13 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.

Srinivas Merugu SSC Enthusiast Points: 133 More actions · Answer 13

Below script will help to get the backup/Restoration % done progress.

----------------

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)

AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],

CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,

CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle)))

FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP LOG', 'BACKUP DATABASE')

*************

select percent_complete,estimated_completion_time,session_id from sys.dm_exec_requests where session_id=xxx

-------------Backup and restore---------------

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time

FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a

WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

*******************************Last Restoration details********************

select * from msdb.dbo.restorehistory where destination_database_name = 'DB_Name' and restore_date>'YYYY-MM-DD'

Is there a way to show backup % done progess?

Cookies on SQLServerCentral