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