Skip to content
logo Knowledgebase

How to move SQL Server Databases for Sage 100 Premium

Created on  | Last modified on 

Summary

Article provides steps to move the SQL Server databases to a new SQL Server instance. The Sage 100 Premium installation remains in place.

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 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
    1.  Restore all .bak files
    2. Right click the database and select Properties
    3.  Select Files, then for Owner, click the Browse button and Select MAS_USER
    4. Select OK to save changes.
    5. Repeat for each of the databases restored
  6.  Relink MAS_Reports account to each company
    1. Manually delete the MAS_Reports under each company Database (MAS_XXX, XXX= company code) Security, Users.
    2. Re-Link the MAS_reports account to each company from SQL Instance Security, Users. Give Read Database and Public access.
  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
    1.  Click  Edit
    2.  Update Server to the new SQL Server name 
    3.  If you have a named instance enter the Server\Instance name of SQL Server
    4.  Update MAS_USER password
    5.  Update MAS_REPORTS password
    6.  Click  Apply
  10.  Select SQL Server Authenication, using Login ID and Password Below
    1.  Logon with SA sign-on and password Credentials 
    2.  Click  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 the Server Name has changed and the Owner shows as MAS_USER
  15.  To verify changes worked logon to Sage 100 Premium
    1.  Open Company Maintenance
    2.  Create a new company code and activate GL and CI and
  16.  Log on 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 are ready to work
  19.  SQL Move will be blind to them