Restore a Single Database View from a Backup

Jim_Knicely · July 2019

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.

[dbadmin@SE-Sandbox-26-node1 ~]$ grep 'objects =' backup_restore_object_local.ini
objects = views

[dbadmin@SE-Sandbox-26-node1 ~]$ 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

[dbadmin@SE-Sandbox-26-node1 ~]$ grep 'snapshotName =' backup_restore_object_local.ini
snapshotName = views_snapshot

[dbadmin@SE-Sandbox-26-node1 ~]$ 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:

[dbadmin@SE-Sandbox-26-node1 ~]$ 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:

[dbadmin@SE-Sandbox-26-node1 ~]$ 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:

[dbadmin@SE-Sandbox-26-node1 ~]$ 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

[dbadmin@SE-Sandbox-26-node1 ~]$ 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!