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

Description

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