Skip to main content
OCLC Support

Move Amlib databases to a new SQL server

Find information about moving 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

  1. Launch Microsoft SQL Server Management Studio
  2. Expand the Databases selection tree [+]
  3. Right-click on the AMCAT database and select Tasks > Back Up...
  4. The Back Up Database window will open
  5. Select the General page
  6. 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
  7. Select the Options page
  8. Choose the following options:
    • Overwrite media
      • Overwrite all existing backup sets
    • Reliability
      • Verify backup when finished
  9. Click the OK button
  10. When complete the following message will appear: The backup of database ‘AMCAT’ completed successfully.
  11. 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

  1. Expand the Databases selection tree [+]
  2. Right-click on the Databases Folder in the selection tree and select Tasks > Restore > Database... – the Restore Database window will open
  3. In the To database field enter the name of the database to load, starting with AMCAT
  4. Select the From device: radio button
  5. Click the ... (Select Devices)button – the Specify Backup screen will open
  6. Click the Add button – the Locate Backup File window will open
  7. Select the corresponding AMCAT.BAK file
  8. Click the OK button to return to the Specify Backup window
  9. Click the OK button to return to the Restore database window
  10. Tick the Restore box for the selected database
  11. 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
  12. Click the OK button – the database will be created and restored
  13. 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.

  1. 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 NETOPACS

    USE AMLIB
    DROP SCHEMA SYSADM
    DROP USER SYSADM
    DROP SCHEMA NETOPACS
    DROP USER NETOPACS

    USE AMLOCAL
    DROP SCHEMA SYSADM
    DROP USER SYSADM
    DROP SCHEMA NETOPACS
    DROP USER NETOPACS

    USE AMSTATS
    DROP SCHEMA SYSADM
    DROP USER SYSADM
    DROP SCHEMA NETOPACS
    DROP USER NETOPACS

    USE 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.
  2. 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)
  3. The Login - New screen will display:
  4. On the General page (select from sidebar):
    1. Select the SQL Server authentication radio button
    2. Enter the following details:
      1. Login name: SYSADM
      2. Password: SYSADM
      3. Confirm password: SYSADM
    3. Deselect the Enforce password policy tick box
  5. On the User Mapping page (select from sidebar):
  6. In the Map column tick the AMCAT database option
  7. 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
  8. Repeat steps 5 and 6 for the AMLIB, AMLOCAL, AMSTATS and AMWEB (where installed) databases
  9. Click the OK button to exit out of this screen
  10. Repeat steps 1 – 8 to add in the NETOPACS user (where the NetOPACs are installed) and/or AMNET user (where AmlibNet is installed) logins
  11. 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

  1. In the Amlib folder on the Amlib server, locate the SQL.ini file
  2. Open the SQL.ini file in Notepad
  3. 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

  4. Save the changes

The databases how now been moved and should be connected to the Amlib client.