Backup SQL Server and create test databases
Learn how to create and backup databases in Amlib.
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 (Please note: Some customers may not have an AMWEB database.)
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 (If applicable) databases
Create a Test Database
The following databases will be created:
- TECAT
- TELIB
- TELOCAL
- TESTATS
- TEWEB (If Applicable)
Create Databases
- Launch Microsoft SQL Server Management Studio
- Right-click on Databases and select New Database... – the New Database window will open
- Database Name = TECAT
- Click the OK button to create the database
- Repeat steps 2-4 to create the TELIB, TELOCAL, TESTATS and TEWEB (If applicable) databases
Restore Backed Up Databases to New Test Databases
- Expand the Databases selection tree [+]
- Right-click on the TECAT database and select Tasks > Restore > Database... – the Restore Database window will open
- 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
- Select the Options page
- Restore options – select Overwrite the existing database (WITH REPLACE)
- Edit the paths of the Data and Log files in the Restore Database files as: table so that they reflect the path of the test database file name – for example: TECAT.mdf and TECAT_log.ldf
- Click the OK button – the database will be restored
- The following message will appear when complete: The restore of the database XXXX completed successfully.
- Repeat steps 2-14 for all the TE databases
Map User Schema
It may be necessary to clear the old user schema first.
- Click the New Query button – this will open up the SQL Query screen
- Type in the following:
- use TECAT
- drop schema sysadm
- drop user sysadm
- Click the ! Execute button
- Repeat for all TE databases
Map User Schema
- Expand the Security selection tree [+]
- Expand the Login selection tree [+]
- Right-click on the SYSADM and select Properties – the Login Properties window will open
- Select a page = User Mapping
- Users mapped to this login: ensure there are ticks against ALL the TE databases
- Database role membership for: ensure that db_owner is ticked
- Click the OK button when complete
Link the Amlib Client to the Test 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:
; 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 - Copy the existing set and then paste underneath, then edit the new set to create the link to the TE
; This is the server paths used for the test SQL server
REMOTEDBNAME=TECAT,DRIVER=SQL SERVER;SERVER=MYSERVERNAME\SQLEXPRESS;DATABASE=TECAT
REMOTEDBNAME=TELIB,DRIVER=SQL SERVER;SERVER= MYSERVERNAME \SQLEXPRESS;DATABASE=TELIB
REMOTEDBNAME=TELOCAL,DRIVER=SQL SERVER;SERVER= MYSERVERNAME \SQLEXPRESS;DATABASE=TELOCAL
REMOTEDBNAME=TESTATS,DRIVER=SQL SERVER;SERVER= MYSERVERNAME \SQLEXPRESS;DATABASE=TESTATS
REMOTEDBNAME=TEWEB,DRIVER=SQL SERVER;SERVER= MYSERVERNAME \SQLEXPRESS;DATABASE=TEWEB - Save the changes
The test database is now linked to Amlib.