How to move SQL Server Databases for Sage 100
Resolution
- Create MAS_User account in new SQL Instance to match the one that exists in Current SQL Instance, Security, Users
- Create MAS_Reports account in new SQL Instance to match the one that exists in Current SQL Instance, Security, Users
- From Source SQL Server Instance create a backup .bak file for MAS_SYSTEM and all MAS_XXX directories
- Copy them to the new server where the new instance of SQL Server will be
- 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
- Click on Ok to save changes
- Now to tell Sage 100 Premium MAS90 side
- From the server in the Sage 100 (version) folder
- 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
- Select SQL Server Authentication Using Login ID and Password Below
- Logon with SA sign-on and password Credentials
- Click on OK
- Sage will let you know the passwords have been reset
- Now go to ..\..\MAS90\HOME directory
- Open MASSQL.SETTINGS file with Notepad or Word
- Note that the Server Name has changed and that the Owner shows as MAS_USER
- To verify changes worked logon to Sage 100 Premium
- Open Company Maintenance
- Create a new company code and activate GL and CI and accept
- Logon to new SQL Server and see if that new database made it to the correct location
- Go into MAS_SYSTEM and verify that SY_SYSTEM picked up the new company code created
- If all is where it belongs then users should be ready to work
- SQL Move will be blind to them
Support
If necessary, seek the assistance of Acute Data Systems to help provide assistance with this process.