Steps to restore Netezza table

Reading Time: 2 minutes

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