Category: Microsoft SQL Server

There are several reasons for backing up your Dynamics GP SQL Data and shared files, among them:

  • Business Continuity
  • Human or Computer Error Recovery
  • Historical Preservation
  • Data Health and Maintenance


Business Continuity or Disaster Recovery is the most comprehensive and usually requires the most planning.  You must consider how much data loss is acceptable and how long your accounting system can afford to be down.

Let’s first consider the types of SQL Recovery Modes available.  It is important to understand these simply because of the types and granularity of restores depend on them.  For our purposes we will focus on the 2 most common recovery models, Simple and Full.

Simple Recovery Model is just that – simple; the backup job creates the backup file and clears committed transactions from the log file, then truncates the log.  It cannot be restored to a point in time other than when it was created.  These can be done as often as needed, but remember, you’re backing up the entire database, so keep a close eye on drive space.

Full Recovery Model (the one we recommend) has many more options, but comes with a bit more overhead.  In full recovery mode, the full database is backed up, including data and log file.  It does not, however, truncate the log file.  There is a secondary, and more frequent, backup called the Transaction Log Backup (never could have guessed that one, right?) that does the actual truncation of the log file.  In a restore situation, you restore the full backup and any log files to get you to the point of error.  This type of backup is your best bet in the event that someone accidentally clears data or other human error situation.

For Business Continuity purposes, you ideally should have a balance of on-disk and external media on- and off-site.  Two or three days should be sufficient for on-disk backups, as you would rarely want to take your accounting system back that many days.  External media retention is up to you, but a week’s worth of backups with at least one day offsite works well for most.

Many companies want or require a year-end backup archived.  Back up the database to external media, and archive as desired.  You may want to burn these to disk as shelf life is longer and would not be affected by EMP, media deterioration or accidental erasure.  Include a copy of the current DYNAMICS database for ease of data access.  Your company may have moved on, but this data if locked in time.

Don’t let your IT tell you that they are snapshotting the server and you don’t need SQL backups.  With full recovery model, you must have both Full and Transaction Log backups to keep the database healthy and keep size and performance in check.

One additional word on Server Snapshots, particularly virtual server snapshots.  If you are running them for Disaster Recovery, DO NOT run them during the work day.  SQL interprets the snapshot scan of the live databases as a disk freeze and any transaction which may be taking place during the few seconds of snapshot scans will likely be corrupted and could require extensive repair of the database if not caught quickly.

One other common error is to have the database set to Full Recovery Model, then let other backup software do the database backups.  This is fine AS LONG AS it also is capable of running Transaction Log backups.  If you only back up the database and not the log, it never truncates and will both cause serious performance issues and finally fill up the disk at which point the database (and GP) shut down.  Know your recovery model, backup type, and frequency.  We can work with you IT staff to develop a plan that works for you.

Test your backups!  Even the best of us can fall into that trap.  The backup looks good, but if you can’t restore it, you might as well not have one.  That’s why my comment on on-disk backups above.  If you need to restore to a point-in-time, have your transaction logs backed up to disk, but your full backup is on some tape somewhere, you’ll be down for as long as it takes to recover the backup media and get it mounted.  Keep several recent backups and relevant log backups on disk for speedy recovery.

There are some non-SQL files that should be backed up regularly as well.  That includes modified reports and forms dictionaries, FRx SysData folder (if you’re still using FRx, and if you are, why?  …but that’s another topic), Integration Manager database, signature files, Mekorma Stub Library, etc..  CAL usually has these under a server ‘GPShare’ folder, so include that in your backups.

Several final notes and frequently misunderstood items:

  1. Backing up your DYNAMICS database does not back up your data. The DYNAMICS database is the GP system database, but only contains system-level information – users, registration, security, etc.  The actual transactions are in your company database.
  2. When you add a company to Dynamics GP, make sure the company database is included in the backup. I usually set the backups for ‘all user databases’ – that way, if a new company is added, it’s automatically included in the backup.  If you use ‘all user databases’, you’re also covered for Management Reporter and SQL Server Report Server backups.
  3. Check your SQL System backups – Master, Model, and MSDB. With those you can recover your GP SQL users in the event of a rare, but possible, SQL application crash.  Without them, you will have to create new users in SQL to tie back to the GP users.

There is a wealth of information about this, but I have to believe that some people just don’t get it.

Let’s start with the easiest, Simple Recovery mode.  You only have one choice for a restorable backup, and that’s a full backup.  When you back up the database, the log (committed transactions) are truncated, allowing the log file (.ldf) to maintain its smallest available size.  There is no transaction log backup for point-in-time restore, so you restore from latest backup.  SQL Express only runs in Simple Recovery mode.

In Full Recovery, you back up the complete database (full backup), but you MUST also back up the transaction log.  If you do not run periodic Transaction Log backups, the log file (.ldf) will continue to grow until it either reaches a preset limit or (more often) fills the drive.  The Transaction Log backup is what clears and truncates the log.  For restoring, you would need the last full backup plus any transaction logs up to the point of desired recovery.

Companies who use a software-specific backup agent for SQL backups are most commonly running full backups to their tape, the cloud, or other media.  If your database is set for Full recovery, you must back up the Transaction Log as well either to the same destination using the agent, or set up a SQL job to back up the log separately.  If this is the case, you would restore from full tape backup then any subsequent transaction log backups to the point of desired recovery.

I’ve seen too many cases where a company will choose a full recovery model to minimize data re-entry in the event of drive or human error, but then only runs full backups to external media.  The result?  A huge log file, or a drive that is out of space, stopping any additional data input until the condition is corrected.

Instructions for fixing full drives are available from Microsoft and many other sites, so I will not cover them here.

If you’re not certain what your database settings are for each of your databases, run this query in SQL 2005 or later:

select name, DATABASEPROPERTYEX(name, ‘Recovery’) Model from sys.databases

Bottom line, use the right backup for your recovery model and method.