How to Backup and Restore Sage 100 Premium (SQL) Databases

Summary

How to backup and restore Sage 100 Premium (SQL) databases

Disclaimer

Database Warning

These steps require knowledge of database engines and application databases (DBs) used by your Sage product (including Microsoft/Transact SQL, Pervasive SQL, or MySQL, etc.). Customer Support is not responsible for assisting with these steps and cannot be responsible for errors resulting from changes to the database engine or DBs. Before making changes, backup all system and application DBs required for a full restore. Contact an authorized business partner or DB administrator for assistance.

Resolution

There are no Sage specific utilities to backup Sage 100 Premium (SQL) databases Microsoft SQL Server and other third-party backup program provide all the ability to automate the backup of your SQL databases

Backing up Sage 100 Premium SQL Database
- Open SQL Server Management Studio
- Select the SQL instance for your Sage 100 Installation
- Enter login credentials then select Connect
- Expand Databases
- Right click on the MAS_XXX database to be backed up (XXX=company code)
- Select Tasks
- Select Backup
- Verify the Backup type is Full
- Verify the Backup Component is Database
- In the Destination filed, note the path and the file name (.BAK file by default)
- Click OK
- Repeat Steps 5-11 for each Sage 100 Premium database; this includes MAS_System
- Exit SQL Server Management Studio

Restoring Sage 100 Premium SQL Database

- Make sure ALL users are out of Sage 100
- Open SQL Server Management Studio
- Select SQL Server Instance for Sage 100 Installation
- Enter login credentials then select Connect
- Expand Databases
  - Note: Make sure you also have a copy of the current database before restoring from backup. In the next step we will delete the existing copy of the database
- (If applicable) Right-click the database you want to restore and select Delete. Click OK to delete database
- Right-Click Databases and select Restore Database…
- Select Device under Source then select the browse button (ellipses ‘…’)
- Click Add, then browse to the location you saved your backup file
- Select the backup file then click OK twice
- On the Restore Database screen, select “Files” from the left panel
- Verify the Restore path is correct. If the path is incorrect select ‘Restore all files to folder‘ and enter desired path
- Select OK
- Right-Click the restored Database and select Properties
- Select Files from left panel
- Type
- ‘MAS_USER’ (without quotes) in the owner filed
- Select OK
- Repeat Steps 5-17 for each Sage 100 databases you need to restore
- Exit SQL Server Management Studio

Support

If necessary, seek the assistance of Acute Data Systems to help provide assistance with this process.