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
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