How to move SQL Server Databases for Sage 100

Resolution

  1. Create MAS_User account in new SQL Instance to match the one that exists in Current SQL Instance, Security, Users
  2. Create MAS_Reports account in new SQL Instance to match the one that exists in Current SQL Instance, Security, Users
  3. From Source SQL Server Instance create a backup .bak file for MAS_SYSTEM and all MAS_XXX directories
  4. Copy them to the new server where the new instance of SQL Server will be
  5. Open Microsoft SQL Server
    • Restore all .bak files
    • After the databases are restored for each one do the following:
        • Right-click on the database and select Properties
        • Select Files
        • For the Owner click on the Browse button
        • Select MAS_USER
  6. Click on Ok to save changes
  7. Now to tell Sage 100 Premium MAS90 side
  8. From the server in the Sage 100 (version) folder
  9. Open Sage 100 Premium (Version) SQL Setting Utility
    • Click on Edit
    • Update Server to the new SQL Server name
    • If you have a named instance enter Server\Instance name of SQL Server
    • Update MAS_USER password
    • Update MAS_REPORTS password
    • Click on Apply
  10. Select SQL Server Authentication Using Login ID and Password Below
    • Logon with SA sign-on and password Credentials
    • Click on OK
  11. Sage will let you know the passwords have been reset
  12. Now go to ..\..\MAS90\HOME directory
  13. Open MASSQL.SETTINGS file with Notepad or Word
  14. Note that the Server Name has changed and that the Owner shows as MAS_USER
  15.  To verify changes worked logon to Sage 100 Premium
    • Open Company Maintenance
    • Create a new company code and activate GL and CI and accept
  16. Logon to new SQL Server and see if that new database made it to the correct location
  17. Go into MAS_SYSTEM and verify that SY_SYSTEM picked up the new company code created
  18. If all is where it belongs then users should be ready to work
  19. SQL Move will be blind to them

Support

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