01 - Reviewing a Backup

01 - Reviewing a Backup

The Universal Database contains tables for each type of data you might backup or restore between systems.  While most of the columns are unique to each table, certain columns exist on all tables.  The purpose of this article is to describe what each column does and how it affects the migration.

 

Backup-Specific Columns

Row

This is an auto-generated row number and should always be ignored.

Never write a query that references the Row column, as multiple backups from the same database will result in different row numbers for the same record.

Id

This is the primary key that the object had in its source system.  It is a string column and will contain string representations of GUIDs, integers, and other string values, depending on the system that was backed-up.  Whenever records are linked to each other, they always link via values in the Id column.


Final_* and Original_* Columns

The Universal Database contains two copies of every value: one prefixed with Final_ and the other prefixed with Original_. 

The Final_ columns represent the values that will be restored into the destination system.

If you need to clean up or transform data, these are the columns that you should update.

The Original_ columns contain the unmodified value that was backed-up from the legacy system.  These columns are intended to be used as a historic reference and should never be modified.
You should never change data in an Original_ column.

Standard Foreign-Key Columns

Standard Foreign Keys always have a name like:
*_Id

Some tables contain values that depend on data from other tables.  These values correspond to the Id column in the related table.

For example, if you run a query like:

  1. SELECT * FROM __M_Matters

You will notice a column named Final_ClientContact_Id.   If a Matter has a Final_ClientContact_Id of 10, then this means that following query will display the related client:
  1. SELECT * FROM __M_Contacts WHERE Id = '10'
 
(Remember - in the Universal Database, Ids are always case-sensitive strings so make sure to always put single quotes around Ids even when they look like numbers.)

Polymorphic Foreign-Key Column

Polymorphic Foreign Keys always have two columns:
*_???_Type and *???_Id

Some tables contain records that can be related to different tables.  For example:
Record TypeUsage
NotesNotes can be attached to any kind of record.
DocumentsUsually a document is nested inside a Matter but sometimes they are inside of Contacts or Invoices.
Postal AddressesUsually a postal address belongs to a Contact but sometimes Matters or Invoices have postal addresses attached to them as well.

When you look at these tables, you'll notice *_Parent_Type and *_Parent_Id columns.
_Type will contain a table name in the Universal Database and _Id will contain the Id of the record in that table.

Special Column Names

Many tables in the Universal Database have columns with similar names.  Throughout the database, we use these columns in the same way.  By knowing their usage, you will be able to get up to speed quickly regarding what data lives where.

*_Status

A Status column will contain one of the following values which indicates how users interact with the record.

StatusUsage
ActiveThis is a real record that users of the application would expect to see and commonly interact with (like an "Open Matter").
ArchivedThis is a real record that users of the application will infrequently use and access.(like a "Closed Matter").
ProspectiveThis is a draft record that is not yet fully created (like a "Pending Matter" or a "Draft Invoice").
DeletedThis is a deleted record that should not be transferred to any destination system.

 *_Kind

A Kind column represents a "Subtype".  Here are examples of record types that often have a Kind.
Record TypeUsage
ContactsThis indicates the kind of contact (Company or a Person).
Custom Field DefinitionsThis indicates the kind of field (Text Box, Picklist, etc.)
CommunicationsThis indicates the kind of the communication (Text Message, Email, etc.)

*_ReferenceCode

A ReferenceCode column contains a human-visible "abbreviation" that firms use to identify/reference a record.  Here are examples of record types that often have a ReferenceCode.

Record TypeUsage
Practice AreasPractice areas often have a ReferenceCode.  For example, the Divorce practice area might have a DIV ReferenceCode.
MattersMatters often have a ReferenceCode (AKA "Matter Number") such as ABC.001.
InvoicesInvoices often have a ReferenceCode (AKA "Invoice Number") such as UIAE-045R.
UsersMatters often have a ReferenceCode (AKA "Initials") such as JJS.

 

*_Subject

A Subject column contains a human-visible short-name/title that quickly summarizes a record.  Here are examples of record types that often have a Subject.

Record TypeUsage
Practice AreasPractice areas often have a Subject.  For example, "Divorce".
MattersMatters often have a Subject (AKA "Matter Name") such as "John Doe v Jane Smith".
EmailsEmails often have a Subject such as "RE: Your initial consult."
NotesNotes often have a Subject such as "Mediation Notes"

 

*_Description

A Description column contains a human-visible multi-line summary/description/body that provides additional details about the record.  Here are examples of record types that often have a Description.

Record TypeUsage
MattersMatters often have a Description such as "John Doe was bit by Jane Smith's dog on 2024-01-01 in Springfield.".
EmailsEmails often have a Description (AKA "Body").
NotesNotes often have a Description (AKA "Note Body").
Time EntriesTime Entries often have a Description (AKA "Time Entry Narrative").

 

*_DisplayOrder

A DisplayOrder column captures the user-definable custom sort order of a record type.  Here are examples of record types that often have a DisplayOrder.

Record TypeUsage
Practice AreaThis indicates what order the practice areas are listed in.
Custom Picklist ItemsThis indicates the order of picklist items.
Contact Postal AddressesThis indicates what address is the primary home address, second home address, etc. for the contact.
Document VersionsThis indicates the version number of the document.

*_Date

A Date column captures the user-specifiable Date for a record type.  This is not necessarily the date a record was created but the date a user chose to associate to a record.  Here are examples of record types that often have a Date.

Record TypeUsage
NoteThis indicates what order the practice areas are listed in.
Custom Picklist ItemsThis indicates the order of picklist items.
Contact Postal AddressesThis indicates what address is the primary home address, second home address, etc. for the contact.
Document VersionsThis indicates the version number of the document.

*_Date_From and *_Date_Till

These columns represent a date range.  Here are examples of record types that often have these columns.

Record TypeUsage
Calendar EntriesThis indicates the start and end time for the calendar entry.
CommunicationsThis indicates the start and end time for the communication (like a phone call).

 

*_Raw_Content

When backing up data from a web-based system, the Raw_Content column contains a copy of the raw JSON metadata that Universal Migrator processed.  If you ever have a question about why Universal Migrator interpreted data the way it did, you can reference the Raw_Content columns to understand why.

Special Tables

Once a backup is complete, there are some special tables you should review.

SELECT * FROM __B_Logs

This table will contain special error log messages that might have been generated as part of the backup.  You should review the logs to make sure nothing significant is reported.

SELECT * FROM __M_Documents_Digital_Files_Versions

This is especially important when backing up an on-premise application.

Document versions are special because they contain a record in the backup that links to a location on a filesystem.  You should review the paths in the Final_ContentPath column and make sure that they are accessible to your migration workstation.  You may need to map a network drive, enter a Window's Username and Password, or do a find-and-replace on values in this column.

    • Related Articles

    • 02 - General Backup Guidelines

      When backing up data, the process will vary from one application to another, however, there are some general guidelines that you should always follow: Always Backup All Data You Will Ever Need When you are creating a backup, always back up all the ...

    • 01 - Tweaking a Backup

      After you have completed a backup, your client may want you to tweak the data before restoring it into their new system. The scripts for any tweaks that you apply should be saved into your Migration Playbook. This will allow you to replay all the ...

    • Training: Overview

      Welcome to the Migration Academy! This series will help you learn everything you need to know in order to deliver high-value, rock-solid data and document migrations. Completion of the academy generally takes 4-8 hours and is required in order to ...

    • 01 - Tweaking a Restore

      Sometimes after restoring a backup, a customer may want you to tweak the data that is in their new system. Tweaking data post-restore is easy! Let's say that the customer wants you to "mark as complete" all tasks that are related to archived (closed) ...

    • 03 - Backing Up a Cloud-based System

      This tutorial will guide you through backing up data from a cloud application. While this tutorial will demonstrate backing up data from Clio, the steps are nearly identical for every cloud-based application. In this tutorial we will: Launch the ...