Migrating Coffalyser.Net™ to another computer or server involves different steps depending on the way that Coffalyser.Net has been installed. This article explains the steps necessary to migrate your installation or database to a new computer or server.
The procedure described in this article is not complicated, but does require some computer skills. When carried out properly, migration should not result in data loss. However, some of the commands in this article have the potential to result in data loss if they are executed on the wrong computer or database. During this procedure you will need to create a backup of your data. It is strongly recommended to retain this backup until you are sure that the new installation works properly. Please read all instructions carefully before starting. We recommend asking your IT department for assistance, if applicable. Contact us for assistance if you are unsure.
General approach
Coffalyser.Net has three main components:
- The Coffalyser.Net client application with which users interact.
- The Coffalyser.Net database, which stores all information including data and user accounts, and is hosted on an SQL Server instance.
- The Coffalyser.Net server service, which facilitates the communication between one or more clients and the database.
All three components may be installed on the same computer, but they may also be installed on separate computers or servers. More information about the possibilities can be found in this article about network setups.
The database is the critical component for migration of a Coffalyser.Net installation, as it contains all data and settings. The general approach to a migration is, therefore, as follows:
- Install Coffalyser.Net on the new computer/server following the normal instructions in the Installation Manual.
- Test the new installation to make sure that it works (optional but recommended).
- Create a backup of the old database.
- Restore the backup on the new computer/server.
- Connect your new installation to the restored database.
Notes to read before you start
- There should be no chance of data loss if the instructions are followed. However, please keep the backup you create in this procedure until you are sure that everything works correctly.
- The instructions in this article assume that you use a preconfigured SQL Server instance. If you use a non-preconfigured manually installed (custom) SQL Server or a network SQL Server, you may need to adjust certain commands (e.g. the instance name and login method) or choose a different backup and restore method (see additional resources). Find out what kind of SQL Server you use.
- If you work in a team on Coffalyser.Net data, or would like to do so, migration is a good time to consider if a different network setup suits your needs better.
- This article uses a command-line tool called sqlcmd for the database migration because it is readily available. You can also use a different database migration (or backup and restore) method if this is more convenient, such as Microsoft SQL Server Management Studio. The Coffalyser.Net database can be migrated like any other SQL Server database. Your IT department may have existing procedures for this.
Step 1: Install Coffalyser.Net on the new computer/server
Perform these steps on the new computer/server to which you want to migrate Coffalyser.Net.
- Install Coffalyser.Net following the instructions in chapter 2.1 of the Installation Manual as for any other new installation.
- Configure the server service following the instructions in chapter 2.2. of the Installation Manual. Keep the following in mind:
- You will be asked to install a preconfigured SQL Server or to connect to an existing SQL Server instance. If you do not need to migrate your SQL Server (e.g. because it is on a dedicated network server and can stay there), you can simply reconnect to the existing SQL Server and database to regain access to your data. There is no need to follow the procedures in the rest of this article.
- If you install a new preconfigured SQL Server or connect to a new custom SQL Server, you will be prompted to enter a name for the database. To simplify the rest of the migration process, specify a name different from your current database name (find your current database name), such as CoffalyserNewServerTest. You will also be prompted for an organisation name and for login credentials for an Admin account. These will only be used for testing.
- After installing a license as described in the Installation Manual, you should have a green server in the server selection dialog. Try to log in to the server to test if everything works.
- If this is a central server to which client applications on workstations of end-users should connect, test this connection from one of the workstations to make sure that there are no network security settings that interfere (troubleshoot connection failures if necessary).
Testing of the new installation is highly recommended to minimize downtime. If you do not want to perform any tests and are using an existing SQL Server, you can go straight to step 2 after step 1.1. If you do not want to perform any tests and want to use a preconfigured SQL Server, you can proceed with step 2 after going through the Configuration Wizard for the first time and installing the preconfigured SQL Server (before creating a new database).
Step 2: Create a backup of the old database
Perform these steps on the old computer/server.
- If multiple users work together on the same data, it is strongly recommended to temporarily shut down the server service to avoid changes being made to the data after the backup is created.
- Press the start button, type in Services, and open the Services app.
- Find the service with name Coffalyser.Net Server. Right-click on it and choose Stop.
- Create a backup of the database. See this article for instructions. Make a note of the database name displayed in the SQL Server Backup Wizard as you will need this later.
Step 3: Transfer the backup to the new computer/server
Take the .bak file with the database backup that was created in the previous step and transfer it to the new computer/server using an appropriate method (e.g. via a network drive or USB drive).
Step 4: Restore the backup on the new computer/server
Perform these steps on the new computer/server.
Restoring the backup can be done using standard SQL Server database restore methods. The instructions below use the command-line tool sqlcmd because it is readily available on most systems. These instructions are specific to a preconfigured SQL Server because they assume a locally hosted SQL Server instance with name COFFALYSER, the use of Windows/integrated authentication, and sufficient access rights to complete the restore. Custom installations of SQL Server may require a different approach (see additional resources).
First check if sqlcmd is available on your system. Open a command prompt (see note above), and enter the command:
sqlcmd -?
This should return help information that starts with
Microsoft (R) SQL Server Command Line Tool
If you receive an error, your system may not have sqlcmd, and you may need to use an alternative method to restore the database backup.
Next, you need to compose the proper command to restore the database backup. The basic format of the command is:
sqlcmd -E -S .\COFFALYSER -Q "RESTORE DATABASE [DATABASE_NAME] FROM DISK='[PATH]'"
In this, replace [DATABASE_NAME]
with the name of your database (which you should have found in step 2 when creating the backup), and [PATH]
with the full path to the backup file. For example, if your database name is Coffalyser and your backup file is located at C:\Temp\Coffalyser_20240201_120000.bak, the command would become:
sqlcmd -E -S .\COFFALYSER -Q "RESTORE DATABASE Coffalyser FROM DISK='C:\Temp\Coffalyser_20240201_120000.bak'"
It is important that the backup file is in a folder that is accessible to SQL Server (personal folders such as your Desktop usually are not), and that the path does not contain an apostrophe.
It may be necessary to rename the database while restoring the backup, e.g. when a database with the same name already exists, or when the path of the data directory used by SQL Server on the old and new computer/server is different. If this is the case, you will see an error message when executing the command. See the section troubleshooting the database restore command at the bottom of this article for instructions on how to modify the command.
Execute the command. The restore was successful if you see an output that ends with a message like
RESTORE DATABASE successfully processed ...
If you obtain an error, check the section troubleshooting the database restore command at the bottom of this article. Not all errors indicate a problem – most can be solved with a simple adjustment of the command. Contact us if you require assistance.
Step 5: Connect to the restored database
Perform these steps on the new computer/server after you have successfully restored the database.
- Open Coffalyser.Net, right-click on the server, and choose Configure to enter the Configuration Wizard.
- Step through the wizard (see the Installation Manual for details if necessary). Set up the connection to SQL Server if not already.
- When prompted for the database name, enter the name of the restored database.
- Continue with the wizard. You should not be prompted to create an organisation and set login credentials for the Admin account. If you are, this means that the Configuration Wizard is attempting to create a new database because it could not find the database you specified. Double-check if the database name is correct. If it is, the restore operation may have failed. Contact us for support.
- Finish the wizard.
- The server selection dialog should now show a green or an orange server. If the server is orange, you may need to install a license (see the Installation Manual). More information about server states.
Step 6: Check if everything is working correctly
Perform these steps on the new computer/server after you have obtained a green server.
- Try to log in to Coffalyser.Net using the credentials that you also used on the old computer/server. Confirm that your data is present. Pay special attention to the presence of recent data (to make sure that a recent backup was used).
- If this is a central server to which client applications on individual workstations should connect, try to connect from one of the workstations to ensure that the network is configured correctly (troubleshoot connection issues if necessary).
Step 7: Cleanup
If everything is working correctly, you can safely decommission the old computer/server. Additionally, you may want to perform these steps:
- If the server selection dialog still shows the old server in red, you may remove it by right-clicking it and choosing Delete.
- If you created a test database in step 1 you may remove it. To avoid accidental data loss, the commands required to remove a database are not provided in this article. Contact us for support if necessary. However, leaving the test database is harmless.
Tips
To minimize downtime, we recommend the following:
- Test the installation on the new computer/server and make sure that it is accessible from the workstations of end-users, if applicable, as suggested in step 1.
- Consider a test migration before stopping the old server and performing the definitive migration. A test migration can be performed using a backup created without stopping the old server (or using an existing backup) so that end-users can continue to use Coffalyser.Net while you try out the migration. Make sure to use a temporary database name that will not conflict with the final database name for the restore operation, unless you are confident that you can remove or overwrite the test migration database. It is also recommended to make sure that end-users do not accidentally connect to the new computer/server with the test migration data, as any changes they make in this environment may be overwritten when the real migration is performed. Contact us for assistance if needed.
Additional resources
Alternative ways to migrate (backup and restore) an SQL Server database can be found in Microsoft's documentation.
Troubleshooting
Reverting to the old server
If you encounter an issue that you cannot immediately solve, you can choose to revert to the old situation. You can simply continue using Coffalyser.Net on the old computer/server as you were used to. If you deactivated the Coffalyser.Net Server service on the old computer/server in step 1, you may need to start it again by following the same instructions as those to stop it but choosing Start instead of Stop.
Troubleshooting the database restore command
The restore command may initially fail for many different (often benign) reasons. If the error message you receive is listed below, you can adjust the command as needed and retry. If you continue to receive an error, contact us for personalized support, contact Microsoft for assistance, or choose an alternative way to restore the database (see additional resources).
The error messages below may differ slightly on your system, depending on e.g. the database name and the path of the backup.
Msg 3201: Cannot open backup device 'C:\Temp\Coffalyser_20240201_120000.bak'. Operating system error 3(The system cannot find the path specified.)
The backup file cannot be found. Ensure that you have entered the path to the backup file correctly. Consider moving the file to a more easily accessible location.
Msg 3201: Cannot open backup device 'C:\Users\Username\Desktop\Coffalyser_20240201_120000.bak'. Operating system error 5(Access is denied.)
SQL Server does not have permission to access the backup file. Files in your personal folders or on your Desktop are usually not accessible by the SQL Server instance. Move the file to a different location with less restrictive access rights. Consider making a temporary folder with access rights for the Everyone user group (ask your IT department for assistance if necessary).
Any message that suggests an invalid syntax, unrecognized option, or unclosed quotation mark
The command contains a mistake. Carefully check the command for typos and missing characters. Ensure that the path to the backup file does not contain an apostrophe.
Msg 3154: The backup set holds a backup of a database other than the existing 'Coffalyser' database
There already is a database with the same name as the database that you are trying to restore. This may happen if you used the same database name for testing in step 1. You can overwrite the existing database, but the required command for this is not given in this article to avoid accidental data loss (contact us if you really need it). A safer alternative is to restore the backup to a different database name. To do this, the command needs to be adjusted to:
sqlcmd -E -S .\COFFALYSER -Q "RESTORE DATABASE [NEW_NAME] FROM DISK='[PATH]' WITH MOVE '[OLD_NAME]' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.COFFALYSER\MSSQL\DATA\[NEW_NAME].mdf', MOVE '[OLD_NAME]_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.COFFALYSER\MSSQL\DATA\[NEW_NAME]_log.ldf'"
In this, [OLD_NAME]
and [NEW_NAME]
refer to the original database name and the desired new database name, respectively, and [PATH]
refers to the path to the backup file. Take care that [OLD_NAME]
and [NEW_NAME]
occur in the command more than once.
The query above contains the path to the data directory used by SQL Server: C:\Program Files\Microsoft SQL Server\MSSQL16.COFFALYSER\MSSQL\DATA in this example, which is the default for a new installation of a preconfigured SQL Server 2022 for both the .mdf and .ldf files. However, this path may differ on a system that has, or originally had, an older version of SQL Server installed. You can find the proper data directory on your system using the commands below. The first command returns the directory in which the .mdf file should be stored; the second command returns the directoy in which the .ldf file should be stored. In most cases, these directories will be the same. Note that these commands may not work on very outdated versions of SQL Server.
sqlcmd -E -S .\COFFALYSER -W -Q "SELECT DefaultDataPath=SERVERPROPERTY('InstanceDefaultDataPath')"
sqlcmd -E -S .\COFFALYSER -W -Q "SELECT DefaultLogPath=SERVERPROPERTY('InstanceDefaultLogPath')"
For example, for a database backup at C:\Temp\Coffalyser_20240201_120000.bak with a database that was originally called Coffalyser but that needs to be renamed to CoffalyserRenamed, of which the data files need to be placed in C:\Program Files\Microsoft SQL Server\MSSQL12.COFFALYSER\MSSQL\DATA:
sqlcmd -E -S .\COFFALYSER -Q "RESTORE DATABASE CoffalyserRenamed FROM DISK='C:\Temp\Coffalyser_20240201_120000.bak' WITH MOVE 'Coffalyser' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.COFFALYSER\MSSQL\DATA\CoffalyserRenamed.mdf', MOVE 'Coffalyser_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.COFFALYSER\MSSQL\DATA\CoffalyserRenamed_log.ldf'"
Msg 5133: Directory lookup for the file 'C:\Program Files\Microsoft SQL Server\MSSQL12.COFFALYSER\MSSQL\DATA\Coffalyser.mdf' failed with the operating system error 3(The system cannot find the path specified.) ... File 'Coffalyser' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.COFFALYSER\MSSQL\DATA\Coffalyser.mdf'. Use WITH MOVE to identify a valid location for the file.
This usually means that the data directory path was different on the old computer/server than it is on the new computer/server. To solve this, it is necessary to 'rename' the database. This uses the same command as the previous error, except that now [OLD_NAME]
and [NEW_NAME]
can be the same. The command works because it includes a specification of the new data path.