QuickBooks Auto Data Recovery – Part 1: “The Theory”

by

Starting in 2010 Intuit began implementing Auto Data Recovery (ADR), a feature designed to allow for recovery of damaged QuickBooks files.  It was first offered in Pro/Premier and then added to Enterprise in 2012.  The feature makes use of two distinct Sybase technologies, ‘auto replication’ which creates a duplicate of your QuickBooks Company (.QBW) file and the QuickBooks Transaction Log (.QBW.tlg) file, and ‘auto recovery’.  Intuit designed their Auto Data Recovery feature to help recover your QuickBooks data in one of two ways:

  • Recreate lost transactions using the current QuickBooks Transaction Log (.QBW.tlg) file and a previous copy of the QuickBooks Company (.QBW) file, or
  • Recreate all but the last few hours of transactions with a copy of both the QuickBooks Company (.QBW) file, and of the QuickBooks Transaction Log (.QBW.tlg) file.

Despite the importance of this feature, most users of QuickBooks, and in fact many QuickBooks ProAdvisors are not even aware of this functionality.  As a result its fundamental ability to rescue files may in go either unused, or become unusable by virtue of the ‘time line’ when someone recognizes the need for the feature.  It is my hope in writing this article that ProAdvisors become aware of this feature, develop an understanding for how it works (‘can do what it can do’) and the specifics of what is required to make use of the feature.

In this three part series we will look first at the ‘theory’ behind the Auto Data Recovery feature which includes the mechanism by which the QuickBooks Company file and QuickBooks Transaction log are produced.  In part two we will look at the step by step procedures for recreating lost transactions using the current QuickBooks Transaction Log with a previous copy of the QuickBooks Company file created by the Auto Data Recovery auto replicate function.  In part three we will look at the step by step procedures to recreate all but the last few hours of transactions with a copy of both the QuickBooks Company file, and the QuickBooks Transaction Log file created by the Auto Data Recovery auto replicate function.

THEORY BEHIND AUTO DATA RECOVERY

First let me interject that we are dealing ‘only’ with the desktop versions of QuickBooks including Pro, Premier and Enterprise.  These principles DO NOT apply to QuickBooks Online.

The QuickBooks Database

QuickBooks has 3 major components, (1) the QuickBooks Application which is what we all think of when we talk about QuickBooks, (2) your QuickBooks Company (.QBW) file that is stored on the computer hard drive and contains your data, and (3) the QuickBooks Database Server (which really is the Sybase Database Server).  There are several other ‘little files’ that tend to be created when you have set-up your QuickBooks Company file, and one of these files is the QuickBooks Transaction Log (.QBW.tlg) file which we will discuss at length in a few moments.

The QuickBooks Database Server

QuickBooks provides two different versions of the Sybase Database Server. The personal database server is a windows service installed when QuickBooks has been set-up as a stand-alone application and QuickBooks and your Company file are on the same computer.  This personal database server is provided for single-use, same-computer use, as an ‘embedded’ database server.  This form of the database server does not support client/server communications across a network.  When QuickBooks is running in single-user mode you will see this server running as a Windows process called QBDBMGR.exe.

But the QuickBooks Database Server can also be installed as a ‘network database server’ when you select any option during installation of QuickBooks indicating that you intend to ‘host’ the QuickBooks file for use by other computers.  This Windows service supports client/server communications across a local area network, and was designed to permit multi-user access. In order to accomplish this task Windows also creates a special Windows User account just for the Sybase Network Database Server, so while the Windows process is called QBDBMGRN.exe (the N which differentiates this process from the personal server service stands for Network), runs under the User profile of QBDataServiceUser## (the ## represents the specific year version of QuickBooks, for example 2015 will appear as 25.)

From the standpoint of managing the actual ‘database serving’ responsibilities, the personal and network server engines are essentially identical, each one support the same database features in exactly the same manner. To this extent the Database server is kind of like an old fashioned ‘traffic cop', controlling the flow of traffic (data traffic); however, the Network version must also perform a variety of other duties including multi-user file locking control.

The File Twins

While not really twins, they go ‘hand-n-hand’. I am talking about the QuickBooks Company (.QBW) file and the QuickBooks Transaction Log (.QBW.tlg) file.  When you create a company file, QuickBooks saves the file with a .QBW extension. For example, if you enter MyBusinessName as the company name, QuickBooks saves your Company file as MyBusinessName.QBW.  The various organized components of the QuickBooks database, such as list and transaction tables, contain the actual data of your QuickBooks company file, and other ‘internal’ elements. This data is stored as “pages” which are fixed size portions of the computers’ hard disk; Intuit has chosen to use a data page size of 4096 bytes. These database pages store the data in a defined format depending on the type of page (data, index, etc.).

At the same time a QuickBooks Transaction Log file will be created with the name MyBusinessName.QBW.tlg. This transaction log is a ‘machine language’ file that records all of the ‘database server’s activities’ including file reads, file writes, table inserts, table deletes, index object creation, link table inserts and many more.  We will look at the mechanics of this file more closely in just a bit.

It is essential that both of these files be preserved, always together.  When you make a backup of your QuickBooks Company file, a copy of the QuickBooks Transaction log is included in the back-up with one exception.  When you select the option, during back-up, to perform a fully verified back-up of your data, QuickBooks determines that is no longer necessary to include the transaction log data in the back-up since the back-up has been determined to represent an exact replica of all the actual data in your database. At the same time the QuickBooks Database Server dumps the data from the existing transaction log, and starts gathering new data into the log.  Because the transaction log can actually grow much larger than your Company file over time, this ‘fully verified back-up’ methodology is the only way to properly ‘reset’ the transaction log.  As such this emphasizes the need to perform fully verified back-ups of QuickBooks regardless of other backup or replication procedures you may be using to preserve copies of your QuickBooks Company file.

The Keeper of the Cache

Regardless of which version of the QuickBooks Database Server (personal or network) is running, it is responsible for flow of data between the QuickBooks application and the QuickBooks company file, it is also the Database Server that make use of your computer’s RAM memory resources Cache in managing the flow of data in order to provide the best possible performance.  Inside your computer, the memory works in conjunction with your processor and hard drive to access and use your data. Let’s say you want to access data within QuickBooks and post some changes to a transaction, here's what's going on inside your computer. (For purposes of this discussion we are going to assume you are running in Single-user mode.)

  1. Both the QuickBooks application and your QuickBooks Company File(s) are resident on your hard drive.
  2. When you click on the QuickBooks Icon your computer’s processor directs the Windows operating system to load the QuickBooks application from your hard drive into to the computer’s memory for short-term access and use.
  3. As soon as you tell QuickBooks to open a specific Company File (even if it defaults to your last open company), the computer’s processor directs the operating system to begin loading your QuickBooks Company File into the computer’s memory; this data is held in a section of RAM memory called ‘cache’. Typically QuickBooks will load only a portion of your Company File into cache (memory), and the portions of the file that it loads depends on several factors including your preferences, the data last or most commonly accessed (such as lists) and have other data you ‘rights to’ (if security protocols are in force).
  4. As you start to perform specific tasks in QuickBooks, your computer’s processor begins to access data from the memory based upon instructions from the QuickBooks database server.
  5. If the processor can’t find the data in memory the database server identifies the information for the processor, which then tells the operating system (Windows) to go find the information on your computer’s disk drive, and to write that data into ‘cache’ (memory).  If cache memory is already full, some of the data in cache memory must be written back to the computer’s disk drive before additional data from the disk drive can be written to cache.

So all of that seems pretty simple, and really it is when we compare it to our next topic, the mechanics of how the QuickBooks database works.  I like to compare the steps above to ‘driving a car’, you know where you want to go, you know how to get there, and you know how to drive, so you get in your car, start it up, and drive to your destination.  But what is going on under the hood of your car to get you there, we just kind of take ‘the motor and drive train’ for granted, don’t we?  Well as long as QuickBooks is running right, and we haven’t lost any data, then we take the ‘goings on inside’ QuickBooks for granted, even if we realize that steps 1 through 5 above occur. But if we are going to understand how the Auto Data Recovery feature files are created, and how they can ‘do what they can do’, then we have to look ‘under the hood’ at the “mechanics” of the QuickBooks Database Server engine.

QuickBooks Database Mechanics

When QuickBooks is ‘shut down’ the QuickBooks Company (*.QBW) file normally holds a complete and accurate copy of all of the pages that encompass our data; the pages have supposed been cleanly written to the computer’s hard disk, as opposed to ‘dirty pages’ which are pages entered into the RAM buffer cache during operations while the database is in use.

When the database is started and users begin to access various tables of data for transactions or lists, the database itself is not generally current rather the database server begins reading the appropriate pages from the QuickBooks file into RAM memory where they are held in cache.  At the same time the database server also makes a copy of the original page (before any changes are made).

These ‘copied’ pages are written in a checkpoint log which is located at the end of the database file. Pages are added to the checkpoint log as needed while the database is in use, and the entire checkpoint log is deleted at the end of the session once all dirty pages (containing new or edited data) are written back to the disk.

At the same time the QuickBooks transaction log is also recording the various activity of the database server as it relates to your transactions. It records the seek and reads of your QuickBooks file on the disk, it records the writes resulting from the creation of new data, or the appends resulting from changes to existing data, it records every insert into a data table as well as any table deletes. The database server creates index objects by which it finds your data, and the related components of that data, all of which are recorded in the transaction log.

But the transaction log must not only keep track of all the changes to the QuickBooks database but it must also automatically record every transaction the application produces for each user of QuickBooks. It is the express purpose of the transaction log to contain enough information about each transaction to ensure that it can be recovered in the event of data loss. 

In order to accomplish these tasks, the QuickBooks database server writes all operations to the transaction log, it then writes log records before any of your data (or related index) pages are ever modified. Next it writes log pages to the Company file on the disk when the ‘commit’ is issued and then it must also notify the QuickBooks Application for any/every user of the successful commit, but only after the Database server has in fact received notification of a successful write to disk from the Windows operating system and I/O sub-system.

In order to enhance performance, changes made to the pages of data are posted to the copy of the data in cache memory; they are not immediately written to the database file on the disk. Approximately once per minute, the database server performs a checkpoint task that checks the number of added pages that have been written to the transaction log since the last checkpoint. If the server estimates that the time required to recover these transactions is greater than the database recovery interval, the database server issues a checkpoint. When a checkpoint occurs, the modified pages are written from cache into your Company file on the hard drive.

After each checkpoint, the checkpoint log’s contents are deleted. The empty checkpoint log pages remain in the checkpoint log during each session and are reused for new checkpoint log data.  At each checkpoint, all the data in the database is held in the Company file, the information within the database file should match the data recorded in the transaction log. The purpose of the checkpoint is to always reflect a specific point in time when a consistent state of the data on the disk existed.

Summary

Did you get all of that, simple stuff isn’t it?  But what this means is that the QuickBooks transaction log, unless it has been ‘disassociated’ from the corresponding copy of the QuickBooks Company file, should contain all the information necessary to restore lost data within a specific time range.  The Auto Data Recovery feature uses an auto replication function to produce copies of the QuickBooks Company file and QuickBooks Transaction log files at regular intervals.  When we learn how the QuickBooks Database Server can use various combinations of these files to identify missing data, and restore it, we can recognize not only the great value this feature poses in many corrupt file situations, but when it is appropriate to use the feature.  So stay tuned for parts 2 and 3 of this series.