Move Amlib databases to a new SQL server
If you have any queries about backup do not hesitate to contact our office.
The following databases must be backed up:
- AMCAT
- AMLIB
- AMLOCAL
- AMSTATS
- AMWEB (If Applicable)
Backup
- Launch Microsoft SQL Server Management Studio
- Expand the Databases selection tree [+]
- Right-click on the AMCAT database and select Tasks > Back Up...
- The Back Up Database window will open
- Select the General page
- Choose the following options:
- Source
- Database: AMCAT
- Backup type: Full
- Destination (can leave as default)
- Click on the Add button to open the Select Backup Destination window
- Click on the ... button to browse to and select a destination
- Click on the OK button
- Source
- Select the Options page
- Choose the following options:
- Overwrite media
- Overwrite all existing backup sets
- Reliability
- Verify backup when finished
- Verify backup when finished
- Overwrite media
- Click the OK button
- When complete the following message will appear: The backup of database ‘AMCAT’ completed successfully.
- Repeat steps 3 – 10 for the AMLIB, AMLOCAL, AMSTATS and AMWEB databases
Load the databases onto the new server
Restore Backed Up Databases to New Databases
- Expand the Databases selection tree [+]
- Right-click on the Databases Folder in the selection tree and select Tasks > Restore > Database... – the Restore Database window will open
- In the To database field enter the name of the database to load, starting with AMCAT
- Select the From device: radio button
- Click the ... (Select Devices)button – the Specify Backup screen will open
- Click the Add button – the Locate Backup File window will open
- Select the corresponding AMCAT.BAK file
- Click the OK button to return to the Specify Backup window
- Click the OK button to return to the Restore database window
- Tick the Restore box for the selected database
- If you would like to specify a location for the data and log files Edit the paths of the Data and Log files, this can be done in the options tab under the Restore As column in the table
- Click the OK button – the database will be created and restored
- The following message will appear when complete: The restore of the database XXXX completed successfully.
Setup User Security
Once all the databases have been restored, you will need to set up the SYSADM and NETOPACS
login.
- First drop the existing users that were migrated with the backups by running the following script in SQL Management Studio you can paste the following into a New Query and then click RUN:
USE AMCAT
DROP SCHEMA SYSADM
DROP USER SYSADM
DROP SCHEMA NETOPACS
DROP USER NETOPACSUSE AMLIB
DROP SCHEMA SYSADM
DROP USER SYSADM
DROP SCHEMA NETOPACS
DROP USER NETOPACSUSE AMLOCAL
DROP SCHEMA SYSADM
DROP USER SYSADM
DROP SCHEMA NETOPACS
DROP USER NETOPACSUSE AMSTATS
DROP SCHEMA SYSADM
DROP USER SYSADM
DROP SCHEMA NETOPACS
DROP USER NETOPACSUSE AMWEB
DROP SCHEMA SYSADM
DROP USER SYSADM
DROP SCHEMA NETOPACS
DROP USER NETOPACS
This script may return some errors such as cannot drop because the user or schema does not exist. These errors are fine however any errors retuned because a user or schema is associated with a table will require the offending table to be deleted. Sometimes the application will create temp tables in the database. - In the sidebar, expand the Security folder, right-click on Logins folder and select New Login... (If you already have a SYSADM and NETOPACS login here please go to step 5 by going to the user properties)
- The Login - New screen will display:
- On the General page (select from sidebar):
- Select the SQL Server authentication radio button
- Enter the following details:
- Login name: SYSADM
- Password: SYSADM
- Confirm password: SYSADM
- Deselect the Enforce password policy tick box
- On the User Mapping page (select from sidebar):
- In the Map column tick the AMCAT database option
- Then tick the db_owner option in the lower screen. Please note you cannot tick all of the databases and then tick the db_owner option once as it needs to be set for each database
- Repeat steps 5 and 6 for the AMLIB, AMLOCAL, AMSTATS and AMWEB (where installed) databases
- Click the OK button to exit out of this screen
- Repeat steps 1 – 8 to add in the NETOPACS user (where the NetOPACs are installed) and/or AMNET user (where AmlibNet is installed) logins
- When you are done, your logins will show under Security > Logins:
These logins should also display under each database > Security > Users:
Link the Amlib Client to the New Database
- In the Amlib folder on the Amlib server, locate the SQL.ini file
- Open the SQL.ini file in Notepad
- Scroll down to the server paths section – you should see the existing server paths for the default (Live) SQL databases and edit the server name entries to match the new server:
; This is the server paths used for the default SQL server
REMOTEDBNAME=AMCAT,DRIVER=SQL SERVER;SERVER=MYSERVERNAME\SQLEXPRESS;DATABASE=AMCAT
REMOTEDBNAME=AMLIB,DRIVER=SQL SERVER;SERVER= MYSERVERNAME \SQLEXPRESS;DATABASE=AMLIB
REMOTEDBNAME=AMLOCAL,DRIVER=SQL SERVER;SERVER= MYSERVERNAME \SQLEXPRESS;DATABASE=AMLOCAL REMOTEDBNAME=AMSTATS,DRIVER=SQL SERVER;SERVER= MYSERVERNAME \SQLEXPRESS;DATABASE=AMSTATS
REMOTEDBNAME=AMWEB,DRIVER=SQL SERVER;SERVER= MYSERVERNAME \SQLEXPRESS;DATABASE=AMWEB - Save the changes
The databases how now been moved and should be connected to the Amlib client.