Backing up your Sage 500 data

2 minute read time.

Are you backing up your Sage 500 data ?  Are you prepared if your SQL Server machine crashes?  If not, you should formulate a plan to recover your data from catastrophe. There are many "best practices" and opinions vary. Here are some thoughts and ideas about backing up your Sage 500 data. You may also want to discuss with your Sage 500 reseller/partner and get their recommendations based on your environment.

You Sage 500 database

In the most basic of configurations, your Sage 500 database is made of up two files that hold data. The files have mdf(master database file) and ldf(log data file) extensions. The mdf file contains all the main data while the ldf records all transactions that occurred(inserts, deletes, updates).  When a basic backup has completed, it is one file that contains within it, the mdf and ldf files.

Recovery models

Microsoft SQL Server provides for three recovery models; Simple, Full, and Bulk-logged.  The most common types I have seen are Full and Simple. The recovery model is a database option that controls how transactions are logged and whether it allows the transaction log to be backed up.
All three  models should be reviewed in detail, and discussed with your Sage 500 reseller/partner, to determine which model is best for your business.

When deciding which recovery model to use for your business, ask yourself how much downtime your business can afford to lose.  If you backup your database once a week and your server crashes, is it okay to lose a weeks worth of data? If the transaction volume is low, perhaps that is acceptable. If volume is high you might need to back up your database nightly, and the transaction log hourly


Schedule backups

While backups can be done manually as needed, you are going to want to automate the process of backing up your database. This can be accomplished from SQL Agent. This means you will need a version of SQL Server that includes SQL Agent. Most SQL Server versions do, but some like the express versions may not.   After a backup SQL job is created, you can create a schedule that will  execute the backup job on a frequency you define. A SQL back up job can be scheduled to run once monthly, weekly, daily, on specific days and times

Test backups

After a SQL back up job has been created, you should test the backup file by restoring the backed up database onto another SQL Server machine. Ideally, it should match the same SQL Server version as your production. After the database is restored, there are a couple of Sage 500 steps required(run the Sage 500 Database Synchronization Utility, and perform a product registration)  

Final thoughts

Test, test, test! Test restoring the backups and after the Sage 500 database has been restored, synchronized, and registered, test a few tasks to confirm they work as expected. 

Do not store the Sage 500 backup files on the same machine as SQL server. If the SQL server machine crashes, you will not be able to access your backup files.  Copy the backup files to another location, back them up to tape, a secure portable drive etc...

Once you have determined your backup strategy, it should be documented with all the details about the process, SQL server names, database names, locations, filenames, any necessary logins and passwords. 

Sage knowledgebase article that describes how you can create a SQL backup job using the Sage 500 Database Creation Utility

Microsoft article related to backup and restore of SQL Server Databases

Microsoft article related to recovery models.