How to move MSS database to a new location?

In this article we will provide step by step instructions on how to move a local or remote MSS database to a new location (another workstation or server). The actions you need to take vary, depending on what version of Medikro Spirometry Software (MSS) you are using and on the type of destination environment. Check the table below, to find the instructions, relevant to your situation. If you can't find fitting combination, contact our technical support or arrange a personal online consultation. 

Table of content 

MSS version	Source database	Target database	Link to instructions
4.x	local (SQLite) databases	another workstation	Go to section 1 >
4.x	remote (SQL Server) databases	another database server (SQL Server Express edition)	Go to section 2 >
4.x	remote (SQL Server) databases	another network location (SQL Server Standard or Enterprise edition)	Go to section 3 >

1. Moving MSS 4.x from local (SQLite) database to another workstation

Please note that these instructions apply for installations using local spirometry databases. If workstations are using a network database (SQL Server), this instruction cannot be used.

1. Install the spirometry software to the workstation according to the Installation Guide.
2. Close the spirometry software, if opened.
3. Copy the local databases (offline.access.db, offline.person.db, offline.study.db) from the old workstation's folder "c:\ProgramData\Medikro\Databases" to the corresponding folder in the new workstation.
4. Start the spirometry software in the new workstation and verify that you can find the right persons by searching for them in the Persons and Studies.

2. Moving MSS 4.x from remote (SQL Server) databases to another database server (SQL Server Express edition)

These instructions apply to the SQL Server Express edition. If you are using the Standard or Enterprise edition, please use separate instructions for the Standard/Enterprise edition.

1) Making backups of the source databases

1. Start Microsoft SQL Server Management Studio. If the source and destination SQL servers have different versions, SSMS version must be the same as the newer one.
2. Connect to the source database server.
3. For the actiondb database, select Tasks->Back Up. Back up the database using default settings. The resulting backup file will be located in SQL Server's Backup folder.
4. Do the same for persondb, studydb and supportdb. Please note that since MSS v4.5 database names have "medikro_" prefix.
5. Please check what is the name of the SQL admin login, that is connected to actiondb, persondb, studydb and supportdb. Name will be needed later.

2) Creating empty databases and logins to the destination server

1. Use Medikro Database Creator to create empty databases for the destination server. Follow the instructions in the Installation Guide chapter "Install Database Server Based Environment"->" Create Databases".
2. In the step "Logon information" give exactly the same admin username that was used in the source server, in order to create an SQL login with the same name.

3) Restoring backups to the destination server

1. Start Microsoft SQL Server Management Studio. Connect to the destination database server.
2. Check that databases actiondb, persondb, studydb and supportdb were created and logins "pbuser" and the admin user.
3. Right-click the database "actiondb" and select Tasks->Restore->Database.
4. In the "General" tab, select Source->Device and select the "actiondb.bak" from the source database backup files. If you are not able to see the bak-files, follow the instructions in the following article: https://stackoverflow.com/questions/38779718/bak-file-not-visible-when-trying-to-restore-database
5. In the "Files" tab, check "Relocate all files to folder".
6. In the "Options" tab, check the "Overwrite the existing database (WITH REPLACE)".
7. Keep the other settings as default.
8. Run the restore by clicking OK.
9. Restoring will disconnect existing logins from database users and they need to be connected. For the database "actiondb" select "New query" and execute following commands:
ALTER USER pbuser WITH LOGIN=pbuser
ALTER [name of the admin login] pbuser WITH LOGIN=[name of the admin login]
10. Do the steps 3-9 for databases persondb, studydb and supportdb.
11. Change the passwords for the logins ""pbuser"" and admin login.

4) Configuring first workstations to use destination server databases
This must be done in a workstation, where Medikro Admin Tool is installed.
1. Make a backup copy of the C:\ProgramData\Medikro\Medikro.ini
2. Start the Admin Tool and select "Manage local application"->"Load settings from local application"
3. In the "Database" tab "Database connection settings", select "(medikro_)actiondb". Change the password for the "pbuser" and change the SERVER name. If the Admin Tool version is 4.5 or newer, you can click "Test the connection".
4. Do the same changes also for persondb, studydb, supportdb.
5. Save the changes by selecting "Manage local application"->"Save settings to local application"
6. Start the spirometry software to check that connection to databases is working. If no connection, make sure that you have typed the password and server name correctly in the Admin Tool. Also, make sure that the firewall is not blocking the connection to SQL Server (ports TCP/IP 1433 and UPD 1434 must be open).

5) Copy the changes in Medikro.ini to all other workstations
1. If every workstation has equal settings, you can replace Medikro.ini on all workstations.
1. If workstations have different settings, you have to manually copy just the changed parts to every workstation's ini (changed password part and server name). Be very careful, when editing the ini file manually to not break it!
2. Test the connections on every workstation.

3. Moving MSS 4.x from network (SQLite) databases to another network location (SQL Server Standard or Enterprise edition)

These instructions apply to SQL Server Standard and Enterprise editions. If you are using the Express edition, these instructions cannot be used because the "database copy" feature is not available in the Express edition.

1) Copying databases from source server to destination server:

Start Microsoft SQL Server Management Studio. If the source and destination SQL servers have different versions, SSMS version must be the same as the newer one.

1. In any of the databases right-click Tasks->Copy Database.
2. Select the source server. Select the authentication method to be used to access the source server.
3. Select the destination server. Select the authentication method to be used to access the destination server.
4. Select the transfer method. We have used the "SQL Management Object Method", which allows source databases to be online.
5. Select databases to copy (actiondb, persondb, studydb, supportdb). Since MSS v4.5 database names have "medikro_" prefix.
6. For all databases, select "stop the transfer, if database with a same name exists in the destination".
7. Select to copy only two logins: "pbuser" and the spirometry admin login that has been created while creating the databases. Its name was defined by the user.
8. Give a name for the integration services package (you can use the default).
9. Select to run immediately.
10. Check the choices in the summary view and Finish.
11. After successful transfer, check that 4 new databases and 2 logins have been created to the destination.
12. Enable new logins in the destination "pbuser" and the admin login because they have been disabled during the process for security reasons.
13. Change the passwords in the destination for logins "pbuser" and admin because they have been changed during the process for security reasons.

2) Configuring first workstations to use destination server databases:
This must be done in a workstation, where Medikro Admin Tool is installed.
1. Make a backup copy of the C:\ProgramData\Medikro\Medikro.ini
2. Start the Admin Tool and select ""Manage local application""->" Load settings from local application"
3. In the "Database" tab "Database connection settings", select "(medikro_)actiondb". Change the password for the "pbuser" and change the SERVER name. If the Admin Tool version is 4.5 or newer, you can click "Test the connection".
4. Do the same changes also for persondb, studydb, supportdb.
5. Save the changes by selecting "Manage local application"->"Save settings to local application"
6. Start the spirometry software to check that connection to databases is working. If no connection, make sure that you have typed the password and server name correctly in the Admin Tool. Also, make sure that the firewall is not blocking the connection to SQL Server (ports TCP/IP 1433 and UPD 1434 must be open).

3) Copy the changes in Medikro.ini to all other workstations:
1. If every workstation has equal settings, you can replace Medikro.ini on all workstations.
1. If workstations have different settings, you have to manually copy just the changed parts to every workstation's ini (changed password part and server name). Be very careful, when editing the ini file manually to not break it!
2. Test the connections on every workstation.