How to move Sage 100 Premium SQL databases to new SQL Server or Named Instance on existing SQL Server

Description

How to move Sage 100 Premium SQL databases to new SQL Server or Named Instance on existing SQL Server

Disclaimer

Backup Warning
Use caution when working with the below product functionality. Always create a backup of your data before proceeding with advanced solutions. If necessary, seek the assistance of a qualified Sage business partner, network administrator, or Sage customer support analyst.
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

  1. Create new SQL (named) Instance
  2. Open Microsoft SQL Management Studio
  3. Connect to “Live” Sage 100 Premium SQL Instance
  4. Created backup of MAS_SYSTEM and MAS_XXX (where XXX = company code(s))
  5. Go to Security, Logins under the SQL Instance
  6. Right-click MAS_Report, select Script Logon as > CREATE To > New Query Editor Window
  7. Select File, Save SQLQuery#.sql As… (where # = the number given to the new query)
  8. Browse to the MAS90 folder.
  9. Select New Folder from the toolbar, the rename folder ‘SQLQuery’
  10. Save Query as ‘MAS_REPORT.sql’ and save in folder created in previous step.
  11. Right-click MAS_User, select Script Logon as > CREATE To > New Query Editor Window
  12. Select File, Save SQLQuery#.sql As… (where # = the number given to the new query)
  13. Save query as ‘MAS_USER.sql’ and place in …\MAS90\SQLQuery\ folder created in Step 9
  14. Right-click SQL Instance under Object Explorer in SQL Management Studio
  15. Select Properties
  16. Select Database Settings under Select a Page
  17. Make note of the Backup location (the default location is C:\Program Files\Microsoft SQL Server\MSSQLxx.InstanceName\MSSQL\Backup, where xx is the SQL version)
    Note: Connect to new SQL Instance and perform Steps 14 – 17 to obtain to its Backup folder location
  18. Open Windows\File Explorer (Winkey+E)
  19. Browse to Backup folder for the “Live” Sage 100 Premium SQL Instance
  20. Copy the MAS_System.bak and MAS_XXX.bak files to the Backup folder for the new SQL Instance
  21. Connect to new SQL Instance using Microsoft SQL Management Studio
  22. Select File, Open, File… from the Toolbar
  23. Open …\MAS90\SQLQuery\MAS_REPORT.sql
  24. Select EXECUTE from the Toolbar (or press F5)
  25. Select File, Open, File… from the Toolbar
  26. Open …\MAS90\SQLQuery\MAS_User.sql
  27. Select EXECUTE from the Toolbar (or press F5)
  28. Right-click Databases and select Restore Database
  29. Select Devices as Source
  30. Select ‘…’ button
  31. Select Add
  32. Select MAS_System.bak then click OK twice
  33. Select ‘Files’ under Select a Page section in upper right corner
  34. Select ‘Relocate all files to folder’ checkbox
  35. Select OK
  36. Repeat Steps 28 – 35 for remaining Databases (MAS_XXX.bak where XXX = Company Code)
  37. Right-click MAS_System database and select Properties
  38. Select ‘Files’ under Select a Page section in upper right corner
  39. Type: MAS_USER, in the Owner field
  40. Select OK
  41. Right-click MAS_XXX (XXX = Company Code) database and select Properties
  42. Select ‘Files’ under Select a Page section in upper right corner
  43. Type: MAS_USER, in the Owner field
  44. Select OK
  45. Repeat Steps 41 – 44 for all remaining company databases
  46. Exit SQL Server Management Studio
  47. Log into the Server where Sage 100 Premium is installed
  48. Open ‘Sage 100 Premium YYYY SQL Settings Utility’ (YYYY = Year\Version) from the Windows Start Menu (Start, Sage, Sage 100 Premium YYYY)
  49. Select EDIT
  50. Enter new SQL Instance Name in the Database Server field
  51. Select Apply
  52. Select SQL Server Authentication Using Login ID and Password Below
  53. Authenticate using SQL ‘SA’
  54. Select OK
  55. Select Close

Support

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