Netezza: Restore a table from an INCREMENTAL Backup

Netezza supports 2 types of backups:

  1. FULL
  2. INCREMENTAL
    • CUMULATIVE – All changes since the last FULL backup
    • DIFFERENTIAL – All changes since the last FULL or CUMMULATIVE

The first step to restore a single table is to see what backups we have.  Note: That NZ calls the FULL backup Increment #1.

Determine what incremental backups we have.  In this case we have a FULL + 1 DIFF backup.

[sourcecode language='sql'  padlinenumbers='true']
$ nzrestore -connector filesystem\
 -dir /backup1 /backup2 /backup3 \
 -sourcedb mySouceDB -incrementlist

Database      Backupset      Seq # OpType NPS Host
------------- -------------- ----- ------ ----------
mySouceDB     20150502072114 1     FULL   MYnetezza
mySouceDB     20150502072114 2     DIFF   MYnetezza
[/sourcecode]

Restore a single table (to a different database) up to the latest backup:

[sourcecode language='sql' ]
$ nzrestore -db MY_TEST -connector filesystem \
-dir /backup1 /backup2 /backup3 \
-sourcedb MySouceDB -tables MyTable

Restore of increment 1 from backupset 20150502072114 to database 'MY_TEST' committed.

Restore of increment 2 from backupset 20150502072114 to database 'MY_TEST' committed.
[/sourcecode]

 

If you want to limit the restore to the FULL (or an intermediate DIFF) add the -INCREMENT argument:

[sourcecode language='sql' ]
$ nzrestore -db MY_TEST -connector filesystem \
 -dir /backup1 /backup2 /backup3 \
-sourcedb MySourceDB \
-tables MyTable -increment 1

Restore of increment 1 from backupset 20150502072114 to database 'MY_TEST' committed.
[/sourcecode]

 

Some general comments:

  • The above backups are for backup files saved on a disk (-connector)
  • The backups are spread across 3 drives (-dir)
  • We are restoring to a different database (-db <> –sourceDB)
  • Multiple tables can be restored by adding their names to the –tables argument, space delimited.  Table names are case sensitive.
  • If the table does not exist, it will be created.
  • If the table does exist, use the –droptables argument for for the table to drop before the restore starts.

I’m sure I will reference this the next time I need to restore a table…

Stay curious; keep learning…

0 Comments

Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>