Skip to content

Posts from the ‘Backup’ Category

Steps to restore table in Netezza

Steps to restore table in Netezza:

1) Find the backup history of database you want to restore by running below query.

 select DBName, DBNAMEORIG, OPTYPE as Backup_Type, StartTime, BackupSet, Logfile
from _v_backup_history
where DBName = 'TEST_DB'
-- DBNAMEORIG = 'TEST_DB'
and starttime > '2013-08-01 05:13:13' 

Output of above sql will be:

DBNAME DBNAMEORIG BACKUP_TYPE STARTTIME BACKUPSET LOGFILE
TEST_DB TEST_DB FULL 2013-08-19 07:40:18 20130819114018 backupsvr.1649.2013-08-19.log
TEST_DB TEST_DB FULL 2013-08-12 06:39:15 20130812103915 backupsvr.29095.2013-08-12.log
TEST_DB TEST_DB FULL 2013-08-05 07:09:39 20130805110939 backupsvr.9521.2013-08-05.log

Here we pull information from column DBNAMEORIG also, this wil be helpful if the database need to be restored is deleted.

2) From above we can find the backup set that needs to be restored
3) Now you can find the path where backup was taken by looking into the log file by going to path /nz/kit/log/backupsvr

It will look like:

 2013-08-12 06:39:15.190780 EDT Info: NZ-00022: --- program 'backupsvr' (29095) starting on host 'testserver-1a' ... ---
2013-08-12 06:39:15.307384 EDT Info: Backup command: /nz/kit/bin/nzbackup -u UTL_DBA -dir /netezza/backup_Path/testserver.domain.com -db TEST_DB -streams 6
2013-08-12 06:39:15.307418 EDT Info: Setting the command line options
2013-08-12 06:39:15.307535 EDT Info: Starting the backup process
2013-08-12 06:39:15.309282 EDT Info: Backing up to base directory '/netezza/backup_Path/testserver.domain.com' 

So, here we can see backup is going to /netezza/backup_Path/testserver.domain.com

4) Now run below script to restore the table.

/nz/kit/bin/nzrestore -dir /netezza/backup_Path/testserver.domain.com -backupset 20130812103915 -tables Table_Name -db ‘TEST_DB’

NOTE: If table already exists then this command will overwrite that table.

Incase you have differential backups and want to restore form there as well then command will be like

/nz/kit/bin/nzrestore -dir /netezza/backup_Path/testserver.domain.com -backupset 20130812103915 -increment 3 -tables Table_Name -db ‘TEST_DB’

Or use below command if you have to restore this table in different database

nzrestore -db TEST_DB1 -sourcedb TEST_DB -backupset 20130812103915 -tables Table_Name -dir /netezza/backup_Path/testserver.domain.com

Netezza backups status showing active

ISSUE

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 conenctions, 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.