Query command for the length of any nodes backup

pfsubaru

ADSM.ORG Member
Joined
Jan 24, 2009
Messages
154
Reaction score
0
Points
0
PREDATAR Control23

Hi,

I have a serveral of Windows SQL DB servers and that I wanted to know the query command to see if I can get their backup start time and their end time. Anyone?
 
PREDATAR Control23

I dont have good answer but first if the backup is scheduled, you might find what you need in 'select * from events'

But as a workaround you can get list of all sessions with start and end time and then you would manually identify what was backup. The select would look like:

Code:
 select SESSION,cast(NODENAME as char(15)) as modename,cast(min(DATE_TIME)
 as char(19)) as start,cast(max(DATE_TIME) as char(19)) as end from actlog where 
not nodename='' group by session,nodename order by start

 

      SESSION     MODENAME             START                    END                 

 ------------     ----------------     --------------------     --------------------
       149399     AAAAAAAAAAAA         2013-06-04-00.01.01      2013-06-04-00.01.01 
       149461     AAAAAAAAAAA          2013-06-04-00.02.59      2013-06-04-00.02.59 
       149146     BBBBBBBB             2013-06-04-00.18.58      2013-06-04-00.19.47


In fact I am using this, because I know when a backup starts so it is no problem to identify backups...
 
PREDATAR Control23

Try this

SELECT entity AS "Node name", CAST(sum(bytes/1024/1024/1024) AS decimal(8,2)) AS "GB xfer", -
(end_time-start_time) as Duration,SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS "start date", -
SUBSTR (CAST(min (start_time) AS char(29)),12,8 ) AS "start time", -
SUBSTR (CAST(max (end_time) AS char(29)),1,10) AS "enddate", SUBSTR (CAST(max (end_time) AS char(29)),12,8 ) -
AS "end time" FROM summary WHERE activity='BACKUP' AND -
start_time> current_timestamp - 24 hours GROUP BY entity,start_time,end_time

Node name GB xfer DURATION start date start time enddate end time
------------------ ---------- ------------------------ ------------------ ------------------ ------------------ ------------------
xxxxxx 0.04 0 00:07:15.000000 2013-07-04 03:53:45 2013-07-04 04:01:00
xxxxxx 0.01 0 00:10:51.000000 2013-07-04 03:02:27 2013-07-04 03:13:18
xxxxxx 0.16 0 00:10:39.000000 2013-07-04 03:00:05 2013-07-04 03:10:44
xxxxxx 0.46 0 00:18:05.000000 2013-07-03 11:02:05 2013-07-03 11:20:10
xxxxxx 0.50 0 00:04:20.000000 2013-07-04 03:40:31 2013-07-04 03:44:51
xxxxxx 0.00 0 00:45:17.000000 2013-07-04 03:01:23 2013-07-04 03:46:40
xxxxxx 0.03 0 00:02:19.000000 2013-07-03 10:41:53 2013-07-03 10:44:12
 
Top