How much longer for the RESTORE or BACKUP?

/*
Hope this helps. Remember Sharing is caring!
BTW, be sure you have this function, Datedifftowords, found here:
http://www.sqldev.org/sql-server-database-engine/regarding-query-performence-97344.shtml
*/

DECLARE @Count INT,
@Counter INT,
@SQL NVARCHAR(MAX),
@sql_handle VARBINARY(64),
@session_id [SMALLINT],
@command [NVARCHAR](16),
@percent_complete [REAL],
@RunTime VARCHAR(50)

DECLARE @tmp TABLE(
idx INT IDENTITY ( 1 , 1 ),
[session_id] [SMALLINT] NOT NULL,
[command] [NVARCHAR](16) NOT NULL,
[percent_complete] [REAL] NOT NULL,
[StartTime] DATETIME
)

INSERT INTO @tmp
SELECT session_id,
command,
percent_complete,
start_time
FROM sys.dm_exec_requests
WHERE command IN ('BACKUP DATABASE','RESTORE LOG','RESTORE DATABASE')

SELECT @Count = @@ROWCOUNT,
@Counter = 1

WHILE @Counter <= @Count
BEGIN
SELECT @sql_handle = sql_handle,
@session_id = t.session_id,
@command = t.command,
@percent_complete = t.percent_complete,
@RunTime = dbo.Datedifftowords(start_time,Getdate())
FROM sys.dm_exec_requests s
JOIN @tmp t
ON t.[session_id] = s.session_id
WHERE t.idx = @Counter

SELECT @session_id [@session_id],
@command [@command],
@percent_complete [@percent_complete],
@RunTime [@RunTime],
TEXT
FROM sys.Dm_exec_sql_text(@sql_handle)

SELECT @Counter = @Counter + 1
END

Comments :

0 comments to “How much longer for the RESTORE or BACKUP?”