Restore a Single Database View from a Backup

The Vertica Backup and Recovery (vbr) 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 accidentally dropped it or deleted a significant amount of data by mistake.

But what about a database view that was accidentally 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 that 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/latest/HTML/Content/Authoring/AdministratorsGuide/BackupRestore/VBRUtilityReference.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/EXPORT_OBJECTS.htm

Have fun!

DBadmin, SQL, VSQL

• Facebook
• Twitter
• LinkedIn