Netezza backup

Reading Time: 3 minutes

Query to find database backup time – Netezza

You can use below query to find database backup time in Netezza environment. This query will provide the database backup time (Full / Differential / User backup) for each database along with the backup start and finish time. Query will not display any data for failed database backup attempts.
Here we will query system view _v_backup_history in system database.

select DBName, OPTYPE, starttime, lastupdate, (lastupdate-starttime)/60 as DurationInMinutes from _v_backup_history

where status='COMPLETED' and DBNAME is NOT NULL

and StartTime>'2012-10-01 00:00:00'

order by DBName, starttime

Unfortunately, we cannot retrieve the backup location as it is not stored in any system table/view in Netezza. For that we have to look into the backup log file.

ISSUE: Backup status shows active

When you check the database backup history using below query for your Netezza you may find some active backups.

select * from _V_Backup_History

CAUSE
Now when you try to check active connections, you are not able to find any backup running. Here you may also see the start time being too old. So why is system table/view showing us wrong information? Reason for this wrong information is that if you kill any backup process directly from OS by issuing Kill Linux process id, then Netezza is not able to understand if backup is really killed and mark it as failed or if backup is still running. So, system table is not updated and you get wrong information.

RESOLUTION
In order to fix this you may have to directly update the system table _T_Backup_History which holds this information.

Below query will return list of all active backups

select * from _V_Backup_History where status = 'ACTIVE'

Below query will return the output as total count of active, completed or failed backups

select distinct status, count(*) from _T_Backup_History group by status

Not, Here is the decoding for status field in _T_Backup_History table.
0 = Active
1 = Completed
2 = Failed

RUn below query to update the system table and mark the actually failed backups (still showing active in system table) to failed.

update _T_Backup_History
set Status = 2 where status=0

ADDITIONAL USEFUL INFO
Query to find active backups in Netezza

Select DBNAME, STATUS,USERNAME,STARTTIME, LASTUPDATE from _V_Backup_History
where DBNAME != 'NULL' and status = 'ACTIVE'

Here we have put DBNAME not equal to NULL because if a database is dropped from Netezza then it updates the backup tale and replace the database name with NULL.