Dynamics GP Backups. They aren't that difficult. Really. Really!!

Over the last few weeks, I've worked with three clients that did not have backups of their Dynamics GP databases, let alone a backup of their modified dictionaries and customizations. This is such a common situation that whenever I work on a client's Dynamics GP system for the first time, I try and always check their backups before I begin any work. Unfortunately, when I check, I often find that they have issues with their backups. Even when a client thinks that they have backups, the backup job often isn't working properly, or there is some issue that would, or does, prevent them from recovering their data when a problem occurs.

Backups aren't difficult. Really, they aren't. There are different approaches and techniques that can make certain types of backups technically complex, but for Dynamics GP, backups don't have to be difficult or complex. In general, I prefer to keep things simple, and backups are no exception. The last thing you want during a data loss crisis or system outage is to have to deal with complexity and the increased potential for breakdowns (both human and technical) that complexity can cause.

I can't cover every detail, so I'm just going to try and cover the basics and offer my thoughts on a few areas based on what I've observed over the years. I welcome comments to fill in any details or offer any other helpful ideas for GP backups. While we're at it, I also welcome any failed backup horror stories that can serve as a lesson of what not to do (I've got plenty to share!).


First, let's talk about the elements of a basic Dynamics GP backup strategy.

1) SQL Server database backups: Make sure to backup your Dynamics database and all of your production company databases. Always make sure to include the Dynamics database as part of your company database backups, since it can contain very valuable information, such as Business Portal data, custom SmartLists created using SmartList Builder, Analytical Accounting setup tables, and user access and GP security settings.

2) Dynamics GP application backups: At a minimum, make sure to backup all of your modified forms and reports dictionaries, the Dynamics.set file, VBA files, and any files installed in the AddIns and Data folders. An easy way to do this is to backup the entire Dynamics GP application directory--it's easy, fast, and often more reliable or less error prone than selecting individual files. If you are using shared dictionaries that are stored in a different location, make sure to backup those files as well.

3) SQL Server security settings backup: If your SQL server fails and you need to migrate to a new SQL server, it can be very helpful to have a backup of your SQL logins and users. The security settings can be scripted, allowing you to quickly recreate a large number of SQL logins. If you only have a few GP users, this step may not be worthwhile, but if you have more than 20 users, I would recommend running the script occasionally to save time in case you do lose your SQL Server.


With those items in mind, let's discuss when and how they should be backed up.

1) SQL Server databases: Ideally, the Dynamics and production GP company databases should be set to use Full recovery model, allowing for point in time recovery. Under the Full recovery model, the production databases should typically have a nightly full backup, and several transaction log backups should run throughout the day, typically every 1-2 hours during business hours. The frequency of the transaction log backups will reflect how much data you are willing to lose in case of a serious issue with the SQL Server. So if your transaction log backups run every 2 hours, you could possibly lose up to 2 hours worth of data entry or posting. If you are willing or able to tolerate the loss of an entire day's worth of data, you could do only nightly full backups and no transaction log backups, or you could switch to Simple recovery model with nightly backups. Using the SQL Server Maintenance Plan Wizard, it is very easy to setup a scheduled backup job that will backup your databases and transaction logs--it takes less than 60 seconds to setup and test a basic backup plan.

A few recommendations: If you use the Maintenance Plan Wizard, make sure that you create and enable a schedule. I recently saw a backup job at a client that had been setup over a year ago, but was never scheduled and therefore never ran, so the client didn't have a single backup. Also, I would strongly recommend NOT appending your backups to a single backup file. The file will quickly become very large and too cumbersome to archive--I just saw another client with a 150GB backup file containing 5 months of database backups. Finally, I personally recommend using a separate directory for each database--it helps to organize the files and makes specific backups easier to find if you have multiple GP companies setup.

When you setup your nightly backup job and transaction log backups, you should save the backup files to a separate physical disk than your database files. If your data drives fail, you don't want your backups to be on those drives as well.

Speaking of drives, I strongly recommend that you perform nightly standard SQL Server backups to disk first, and keep at least 3-5 days of backups on disk so that they are easily accessible. You can then backup those SQL backup files to tape or an archive disk for longer term storage. And I also strongly recommend that an organization keep several months of monthly backups (at least 3-6), since I have had several situations where the client needed to recover something that was older than the company's 2 week or monthly backup rotation.

I personally do not ever recommend using backup software to backup SQL databases directly to tape. It theoretically works, but when you need to urgently recover your GP databases, the last thing you want to do is have to rely on tapes and 3rd party backup software as your only backup source. I've been there several times, and it wasn't fun. Disk space is now so cheap that the traditional arguments for backing up SQL databases directly to tape no longer apply (at least for Dynamics GP). I see that 1 terabyte hard drives are now as low as $80, so the cost of storage is no longer a concern. Tape is fine as one element of an overall backup strategy, but I strongly recommend that you always have some SQL backup files that are accessible on disk.

2) Dynamics GP application: My personal favorite is to just manually use WinZip or WinRAR to zip the entire Dynamics GP application directory to a single file. Using a naming convention like "YYYY-MM-DD Dynamics GP Backup.zip", it's easy to manage the files and archive them. The resulting compressed file is usually less than 500MB, and is very convenient to use for a selective file restore, or a restore of an entire GP install. This backup doesn't have to be performed very often--typically only after a customization is added, a new GP module or 3rd party product is added, or GP is upgraded as part of a service pack or hotfix. I admit that it is very easy to forget to perform this backup after all of those events, so it may be a good idea to just schedule a monthly backup.

3) SQL security settings: Steps 1 and 2 of Dynamics KB Article 878449 provide instructions for using the "Capture_Logins.sql" script to backup all SQL Server logins and password information. If you have alot of GP users, this backup can be very helpful and can save alot of time for a server restore, migration, or simply setting up a new test or QA server. If you ever have to urgently recover your GP environment to a new SQL Server, having the logins scripted can reduce your stress and make a recovery much smoother. Like the Dynamics GP application backups, this step does not need to be performed very often. As long as you have the majority of your users in the script, you can easily add a few new users if necessary.


Once you have all of these backups setup and running successfully, make sure to have an on-site and off-site archiving strategy. You could buy two 1TB external drives and rotate them weekly to a relatively safe, relatively secure, off-site location. It's easy to find very real news stories about floods (Fargo), hurricanes (Texas, Louisiana, Florida, etc.), and my favorite Los Angeles recreational sport, earthquakes, to know that your building, server, and data are not immune from being completely destroyed. Online backup services have become so inexpensive and convenient that they are also an option for certain types and amounts of data. One caveat, however, is that if you are using the Dynamics GP Human Resources or Payroll modules, you will want to be especially careful with your backups. States such as California have laws that govern the storage and loss of private data, and common courtesy dictates that you care for SSNs and employee info, so if you do have sensitive data, you may want to consider encryption options for your off site backups.


Now that I've described what needs to be backed up, and how it should be backed up, I'll very simply describe the most important step of all.

TEST YOUR BACKUP PLAN USING A RECOVERY PLAN!

For most IT departments, this step is right up there with dieting, going to the gym, taking the daily vitamin, and flossing twice a day. Everyone knows that they should do it, but nobody ever actually does it diligently. But a recovery plan is actually easier than all of those other good practices, since you don't have to do it every day. A few times a year would be fantastic compared to most companies.

Here's how you can test your backups from scratch in less than two hours:

1) Install a new instance of SQL Server on a Windows server machine (it can be desktop with Windows Server 2003)
2) Install Dynamics GP on the machine
3) Unzip your GP application directory backup and overwrite the GP files on your machine
4) Restore your most recent GP database backups
5) Run the SQLLOGINS.sql script that you generated from Capture_Logins.sql
6) Create a DSN to point to the SQL Server instance
7) Launch GP, login as both sa and a standard user, and verify the data and users

And once that test system has been setup, future tests of your recovery plan would probably take less than 30 minutes, as you should only need to complete steps 3, 4, 5, and 7.

If you actually implement a backup strategy similar to what I described above, and if you actually TEST your recovery plan, you will definitely be in the 99th percentile relative to your peers.

Live long and prosper.



UPDATE:

Like I said, I couldn't cover all of the details. I see that Mark Polino also recently posted an article on Dynamics GP backups, and he has some great tips on backing up several other important items related to GP that are typically forgotten in backup routines.

And I found a few other backup anecdotes here and here, and here is an old computer joke that also makes the point.