Your Essential Guide to Database Management in Sage 300: Tips for Management, Backups, and Recovery

Database management is more than just a geeky software term; it’s a critical piece of your business operations. Picture this: it’s Monday morning, you haven’t yet had your coffee, and your database decides to throw a fit. Whether it’s from a software update gone awry, a security breach, or something entirely different, the results are the same: chaos.

Sound familiar? If not, consider yourself lucky, and keep reading, because luck doesn’t hold forever.

Database Management and Backup

The cost of cybercrime continues to rise with the average cost of an attack in 2023 sitting at around $3.86 million. With an increase in remote work, we expect this number to grow more every year. This makes effective data management and regular backups not just a good idea but essential for safeguarding your data.

Instructions on Performing Database Dumps and Loads

Regular database dumps (backups) should be part of your routine maintenance. As you’d expect, this process involves creating a complete copy of your Sage 300 database, which can be restored if needed. 

Just like when you’re testing macros, loading a database (restoring from a backup) should be practiced in a test environment to make sure that you're prepared in case of a data loss incident. This is an important step that companies often miss, unfortunately.

How to Create Backups and Restore Data

Once again, we can count on automation for help with database management. Backups can be scheduled to occur automatically at regular intervals, to make sure your data is consistently protected. It's crucial to store these backups in a secure, off-site location to prevent data loss due to physical damage to or compromise of the environment. 

In the event of data corruption or loss, having a recent backup and having practiced restoring it means your team can quickly & easily restore your data to a previous state, minimizing disruption to your business operations, and saving your business a ton of money and time.

Database Restoration

If the Sage utility encounters issues or fails to complete a process, restoring the database becomes necessary. The restoration involves a database load procedure, requiring an exit from the database. It’s a lot of data drama, but this step is crucial, especially if the account code change affects some tables but not others, which can lead to data corruption​​.

Dealing with Data Integrity Errors

82% say data quality concerns represent a barrier to their data integration projects, which is why we must make every effort to minimize data errors. Data errors are common, and occur during day-to-day operations, like when a user deletes items. To manage these errors, one solution is to combine similar items into a single code, like 'ZZZ deleted item,' which consolidates all historical transactions into one item. This approach helps avoid hundreds or even thousands of data integrity errors​​. You’ll thank us later for that tip.

Importance of Backup

Despite the increasing reliance on SaaS applications among modern businesses, fewer than one in five organizations back up their SaaS data. It might sound like we’re repeating ourselves (we are because it’s important!), but backing up company data before making any number changes in Sage is critical. If the process stops midway, there could be partial changes in some tables and not in others, leading to damaged/corrupted data. Always back up the data to ensure that it can be restored in case of a crash or other issue.

Enhancing Data Security and Integrity

Raising the bar for data security and integrity in Sage software isn’t a walk in the park. It involves several detailed steps to ensure both the safety of the data and the accountability of changes made within the system.

1.     Exclusive Access to the Database

When posting changes using any of the account code/number change modules in Sage, it is necessary to have exclusive access to the database. When the number changer utilities are making their updates, there cannot be any other changes to the database or there is a significant risk of causing corruption due to inconsistencies across the tables​​. By locking the database during the number changer process, it is able to complete its tasks without concern for other changes for which it could not go back and update.

2.     Audit Logging for Accountability and Transparency

The number changer utilities also include a handy logging feature that creates audit logs after changes are made and posted. These logs are available in the message log section, and contain tabs for audit, error, and warning messages. For those not familiar, an audit log provides a detailed record of all activities, including what changes were made, by whom, and when.

3.     Error Checking and Reviewing Change Lists

Errors in your data can lead to poor decision-making. Implementing a practice of creating and reviewing change lists before posting changes can help catch potential mistakes before they actually happen. This allows for a more controlled and deliberate approach to database management modifications, reducing the likelihood of errors.

4.     Scheduled Posting of Changes

To build off our last tip, it is recommended to post changes during off-hours or when no other users are in the database. This approach eases the inconvenience caused by exclusive access.

Navigating Sage’s importing and exporting of data, particularly when wrestling with different versions of Excel, highlights the need for adaptability and some serious problem-solving skills. Luckily, you have our help with that! The importance of exclusive database management and access during critical changes, alongside robust audit logging and error checking, cannot be overstated. Our tips and tricks outlined here will help you preserve the integrity of your financial data and ensure accountability in every transaction and modification. Whether you’re a data dabbler or a spreadsheet sorcerer, don’t be shy to reach out to one of our Sage experts for importing and exporting help.