The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Restore a Single Database View from a Backup

The vbr (Vertica Backup and Recovery) utility allows you to back up and restore either the full database, or one or more schema and table objects of interest. It’s a great tool for restoring a table if someone accidently dropped it or deleted a significant amount of data by mistake.

But what about a database view that was accidently dropped? How would you get it back from a vbr backup if you haven’t exported the view’s SQL using the EXPORT_OBJECTS function?

Unfortunately you cannot restore a single view from a backup using vbr directly. You can only restore the entire schema in which that view resides. But the good news is the SQL for the backed up view is stored in plain text in the backup files! You can find that code and re-create your lost view and become a DBA hero!

Example:

dbadmin=> CREATE SCHEMA views;
CREATE SCHEMA

dbadmin=> CREATE VIEW views.v1 AS SELECT * FROM dual;
CREATE VIEW

dbadmin=> CREATE TABLE views.table1 (c INT);
CREATE TABLE

dbadmin=> INSERT INTO views.table1 SELECT 1;
OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO views.table1 SELECT 2;
OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO views.table1 SELECT 3;
OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> CREATE VIEW views.v2 AS SELECT * FROM views.table1;
CREATE VIEW

dbadmin=> \q

As part of my backup strategy, I take regular schema level backups.

[[email protected] ~]$ grep 'objects =' backup_restore_object_local.ini
objects = views

[[email protected] ~]$ grep -A 3 'indicates backup to localhost' backup_restore_object_local.ini
; [] indicates backup to localhost
v_test_db_node0001 = []:/home/dbadmin/backups
v_test_db_node0002 = []:/home/dbadmin/backups
v_test_db_node0003 = []:/home/dbadmin/backups

[[email protected] ~]$ grep 'snapshotName =' backup_restore_object_local.ini
snapshotName = views_snapshot

[[email protected] ~]$ vbr -t backup -c backup_restore_object_local.ini
Starting backup of database test_db.
Participating nodes: v_test_db_node0001, v_test_db_node0002, v_test_db_node0003.
Objects: ['views']
Snapshotting database.
Snapshot complete.
Approximate bytes to copy: 0 of 106 total.
[==================================================] 100%
Copying backup metadata.
Finalizing backup.
Backup complete!

Later, someone runs this:

[[email protected] ~]$ vsql -c "DROP VIEW views.v2;"
DROP VIEW

Yikes! They meant to drop VIEWS.V1! How do I get back VIEWS.V2?

Buried in a file named <>.rst I can find the SQL for the view:

[[email protected] ~]$ grep -A 3 v2 /home/dbadmin/backups/Snapshots/views_snapshot_20190729_010938/v_test_db_node0001/views_snapshot.rst | grep queryString
    queryString:SELECT table1.c FROM views.table1

Now I can re-create that view like so:

[[email protected] ~]$ grep -A 3 v2 /home/dbadmin/backups/Snapshots/views_snapshot_20190729_010938/v_test_db_node0001/views_snapshot.rst | grep 'queryString:' | sed 's/queryString:/CREATE VIEW views.v2 AS /g' | vsql
CREATE VIEW

[[email protected] ~]$ vsql -c "SELECT * FROM views.v2;"
c
---
1
2
3
(3 rows)

Phew! Catastrophe averted!

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/BackupRestore/VBRUtilityReference.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/EXPORT_OBJECTS.htm

Have fun!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.