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

Description

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

Create new SQL (named) Instance
Open Microsoft SQL Management Studio
Connect to “Live” Sage 100 Premium SQL Instance
Created backup of MAS_SYSTEM and MAS_XXX (where XXX = company code(s))
Go to Security, Logins under the SQL Instance
Right-click MAS_Report, select Script Logon as > CREATE To > New Query Editor Window
Select File, Save SQLQuery#.sql As… (where # = the number given to the new query)
Browse to the MAS90 folder.
Select New Folder from the toolbar, the rename folder ‘SQLQuery’
Save Query as ‘MAS_REPORT.sql’ and save in folder created in previous step.
Right-click MAS_User, select Script Logon as > CREATE To > New Query Editor Window
Select File, Save SQLQuery#.sql As… (where # = the number given to the new query)
Save query as ‘MAS_USER.sql’ and place in …\MAS90\SQLQuery\ folder created in Step 9
Right-click SQL Instance under Object Explorer in SQL Management Studio
Select Properties
Select Database Settings under Select a Page
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
Open Windows\File Explorer (Winkey+E)
Browse to Backup folder for the “Live” Sage 100 Premium SQL Instance
Copy the MAS_System.bak and MAS_XXX.bak files to the Backup folder for the new SQL Instance
Connect to new SQL Instance using Microsoft SQL Management Studio
Select File, Open, File… from the Toolbar
Open …\MAS90\SQLQuery\MAS_REPORT.sql
Select EXECUTE from the Toolbar (or press F5)
Select File, Open, File… from the Toolbar
Open …\MAS90\SQLQuery\MAS_User.sql
Select EXECUTE from the Toolbar (or press F5)
Right-click Databases and select Restore Database
Select Devices as Source
Select ‘…’ button
Select Add
Select MAS_System.bak then click OK twice
Select ‘Files’ under Select a Page section in upper right corner
Select ‘Relocate all files to folder’ checkbox
Select OK
Repeat Steps 28 – 35 for remaining Databases (MAS_XXX.bak where XXX = Company Code)
Right-click MAS_System database and select Properties
Select ‘Files’ under Select a Page section in upper right corner
Type: MAS_USER, in the Owner field
Select OK
Right-click MAS_XXX (XXX = Company Code) database and select Properties
Select ‘Files’ under Select a Page section in upper right corner
Type: MAS_USER, in the Owner field
Select OK
Repeat Steps 41 – 44 for all remaining company databases
Exit SQL Server Management Studio
Log into the Server where Sage 100 Premium is installed
Open ‘Sage 100 Premium YYYY SQL Settings Utility’ (YYYY = Year\Version) from the Windows Start Menu (Start, Sage, Sage 100 Premium YYYY)
Select EDIT
Enter new SQL Instance Name in the Database Server field
Select Apply
Select SQL Server Authentication Using Login ID and Password Below
Authenticate using SQL ‘SA’
Select OK
Select Close

Support

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