Skip to content
logo Knowledgebase

How to move SQL Server Databases only but not Sage 100 Premium

Created on  | Last modified on 

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