Originally, QuickBooks Desktop effectively operated more like an Excel workbook, storing data in flat information files. QuickBooks later changed to use an actual database. Fortunately (and unfortunately) a database engine is a very complex thing and while Intuit has provided a lot of tools to help manage the complexity, too often those tools and best practices are not used. My goal in this article is to help you understand why maintenance tools are needed to make sure your QuickBooks database stays safe.
The information in this article is a preview of one of my sessions at Scaling New Heights, 2021. I hope to see you there in my breakout sessions "QuickBooks Desktop Reporting: A Deep Dive into Using the Data You Work so Hard to Put into QuickBooks" and "Making a Great Dashboard with Power BI."
To maximize your use of QuickBooks, there are several core ideas you need to understand.
The QuickBooks database is a complex structure.
When you look at a visual representation of the QuickBooks Desktop’s database (and you are not meant to be able to read the words in this image), you will see the scope of the database.
As accounting professionals, we don’t enter data just because we want that data. Instead, we put data into the QuickBooks database to be able to get aggregated information out of QuickBooks. Although that low-level data on its own is useful, the higher-level information we extract is more important. For example, when you enter a sales invoice, what you may want is the information around historical sales for compliance (tax) or maybe to spot trends to project future sales.
Because none of us would put up with long wait times to save, retrieve or maintain long strings of redundant data inherent in financials transactions, QuickBooks was designed primarily as what is called an Online Transactional Processing (OLTP) database. These databases are characterized by a complex interrelationship of separately stored information that, in many cases, are linked by codes that identify a unique record like an account, an item or a customer. The ability to store just a small code instead of a long customer name not only saves space and reduces the speed at which an invoice can be saved, it also reduces the number of records that need to be updated when a name changes.
Let’s take a transaction level look at what happens in the database. When you create an invoice, for example, you are actually entering data into ten different tables within the database - summary information, customer information, details, inventory line, serial/lots, linked transactions, audit trail, preferences, entity notes, and billing rates. When you consider that each inventory item may impact multiple inventory lines with multiple series and lots and that you create audit trail changes, you begin to appreciate how complex the data structure can become.
Stuff happens.
As you can see, the QuickBooks Desktop database is a very complex series of tables and while Intuit engineers work incredibly hard to ensure that no data is ever lost and while they do all they can to prevent mistakes, ALL applications and database as complex as QuickBooks will have "stuff" happen.
People sometime refer to a “corrupted QB file” but I’m not sure that’s a great way of understanding what is really happening. The Sybase database engine which is at the heart of QuickBooks after all is world class and can store way more information than our QuickBooks files hold, so what’s the real issue?
Simply put, what most people see as corruption stems from is an unexpected situation that comes about while updating QuickBooks.
As a database professional that sees hundreds of files every year, we certainly see some patterns erupt in corruption. Because updating a file is realistically the only way a file becomes “corrupted”, all of these factors below are all just different ways the risk is increased.
All backups are not the same.
Many companies tell me that the tech guy handles their QuickBooks backup. That would be great EXCEPT for the fact that most tech guys are going to use their tools to save a copy of the QuickBooks files and call it a day. That may work (I know it may have worked for you for 20 years), but it is not bulletproof. The reason this kind of backup is not very reliable is related to the fact that, as detailed earlier, QuickBooks is a complicated database file.
What should we do then?
I mentioned maintenance tools at the beginning of this article. The number one underutilized tool in my opinion is the QuickBooks automated backup. Setting up the backup with verification AND monitoring it will not just give you a fallback position, it can alert you to issues in your data before they’re insurmountable.
Here’s how: Back up your QuickBooks Desktop company file (intuit.com).
Figure 1 QuickBooks scheduled backup and resulting Task Scheduler event
Since QuickBooks is deployed in a lot of environments, a caveat here; you should always do your own testing and validation before relying on a backup plan. Hosted environments, Qbox and other add-in products can all affect this Intuit tool.
Finally, the biggest reason to do the Intuit backup instead of a file backup is that before this backup runs, Intuit made sure to include a maintenance program that verifies the database BEFORE doing a backup. This verification is critical to the long-term health of your database.
A little tidbit that might intrigue you here; as a part of the maintenance you may save some disk space and improve the speed of QuickBooks. I'll cover the details of that in another article. For now, get your routine backup / maintenance scheduled!