A good backup routine is like an insurance policy for your Microsoft Dynamics GP system. You hope that you never need to use it, but if you do, you are so grateful you have it.

This post contains information on how a SQL backup should be taken so that it will not interfere with the backup chain or any other operations.

Standard Operating Procedure for SQL Backups – What You Need To Know

When taking a manual backup, it is important to do the following:

    1. ALWAYS check “Copy-only backup”

      This ensures the backup will not break the backup chain.
    2. Verify that the server has enough disk space.
      • Some of our larger clients have very large database sizes. Backup up the databases can cause all of the disk space to be used which could have devastating consequences.
      • Hint: You can compress backups so they take less size (typically up to 50% less but can vary wildly)

  1. Verify the location of the backups.
    • If you save the backup inside of a folder where the backups are removed on a schedule, your backup may be deleted.
    • If you save the backup outside of the folders that are monitored, your backup must be removed manually when you are finished with it or it will take up space unnecessarily.

Backup Chains

A backup chain is a specific sequence in which all database backups should be restored. If the backup chain is broken, the restore process will be not possible and some data will be lost.

Types of Backups

In SQL Server, there are 3 different types of database backups: Full, Differential and Transaction Logs. To use Transaction Log backups, the database must be set to Full Recovery Model. Full backups are available for all databases.

Note: There are other types of backups such as File, FileGroup, Partial and others. These are not in the scope of this document, as they are less common than the ones mentioned here.

  • Full
    • This will back up all data in the database. A restore can be completed from this file alone and it will contain all of the data up to when this backup was taken.
  • Differential
    • Differential backups contain only the data that was changed since the latest full backup. Differential backups are cumulative, meaning if additional differential backups are taken, they will still contain all of the data since the latest full backup.
  • Transaction Log
    • A transaction log backup contains transactional data from the either the last full backup or transaction log backup (whichever came last). Transaction Log backups allow you to restore to a specific point in time. These backups are incremental, not cumulative like differential backups.

Using the Backup Chain

All backup chains start with a full backup. Typically, our clients have small databases, so a full backup every night is not a big deal. However, once you get into larger databases, it may not be feasible to keep that many full backups on hand. This is where the differential backups can come in.

Differential Example

Let’s take a look at a common backup scenario. Some companies take a full backup on Sunday night, then a differential backup on the other nights of the week. In this way, you only have to store new transactions nightly – not the entire database. If you wanted to restore from the differential backup on Thursday, you would full restore the full database backup (with the NORECOVERY option) then restore the Differential from Thursday (with the RECOVERY option). Now your database is ready for use.

Transaction Log Example

Now suppose you wanted to restore your database at 10:17AM on Thursday. To do this, you would need to include transaction log backups. Let’s say the company you are working with takes a Full Backup on Sunday at 12:00AM, Differential Backups Mon-Sat at 12:00AM and transaction log backups every hour. In this case, you would restore the Full backup from Sunday, then the Differential backup from Thursday at 12:00AM, then every transaction log backup up to the 10:00 AM backup. All of these will be done with the NORECOVERY option, which allows you to continue to restore the backups from the chain. Then you would restore the 11:00 AM backup using the RECOVERY option and with the STOPAT option set to 10:17AM (this is called “Restore To” in Management Studio). Now the database is ready for use.

Breaking the Database Chain

Now let’s take a look at the same situation as above, but let’s say that a user unknowingly took a full database backup on Wednesday afternoon. This backup will have broken the backup chain (see fig.1 below). Now, the Thursday 12AM Differential backup will only contain the data from the Wednesday afternoon backup until Thursday at 12AM. Therefore, the SQL DBA will need to start from the Wednesday afternoon backup, which they may not be aware of or not have access to. To prevent that from happening, the user should have taken the full backup with the COPY_ONLY option. Using the COPY_ONLY option does not break the backup chain (see fig.2 below).

Note: If all of the transaction logs are still available, the DBA can restore from the original full backup, then the differential from Wednesday at 12AM, then every transaction log backup from Wednesday at 12AM until Thursday at 11AM.

Fig. 1: Breaking the backup chain

Fig. 2: Using COPY_ONLY

Additional Information

For additional information, see the following sites:

Note: some images and information was taken from these sites.

Customer Scenarios

Many of our Microsoft Dynamics GP clients do not take this process as seriously as they should. Or some of them think they are doing backups properly when in fact they are not.  For example, we have seen that some clients will schedule a full nightly SQL backup to disk at 9:00pm, then their backup software comes through a couple hours later and writes a backup to tape.  In the morning, after several transaction log backups have taken place, they need to restore.  The full backup on the disk is now useless, as the one on tape is now the last full and needs to be paired with on-disk transaction log backups.  Lots of time is lost while they locate the correct tape because although they have the hardware and the plan, they don’t know how to restore from it.

Action Plan

This document contains only a small part of SQL Backups. At CAL, it is very important for us to honor our customers’ backup plans and not do anything that would jeopardize a potential restore. For the best results, make sure you are follow these steps.

As a CAL client, we encourage you to schedule a review of your Dynamics GP backup plan with our team. It is the best insurance policy for your Microsoft Dynamics GP system. Contact us at 860-485-0910 or support@calszone.com.

By CAL Business Solutions, Microsoft Dynamics GP and Acumatica Partner, www.calszone.com