This background article contains troubleshooting suggestions for the Coffalyser.Net database migration procedure. If you encounter an issue that is not listed in this article, please contact us for personalized assistance.

Reverting to the old server during troubleshooting to minimize downtime

If you encounter an issue that you cannot immediately solve, you can consider reverting 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 of the migration procedure, you may need to start it again by following the same instructions as those to stop it but choosing Start instead of Stop.

When you solve the issue and want to continue with the migration, check if it is necessary to create a fresh backup of the database to avoid losing new data entered into the old database during troubleshooting.

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 accessible location such as C:\Temp.

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 such as C:\Temp 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 small mistake. Carefully check the command for typos and missing characters. Copy the example commands again from the migration article if necessary. 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

A database with the same name as the database that you are trying to restore already exists. This may happen if you used the same database name for testing in step 1 of the migration procedure. 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 directory 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, you can use the procedure to 'rename' the database as described immediately above. You can follow those instructions exactly, but you can leave [OLD_NAME] and [NEW_NAME] the same. The command works because it includes a specification of the new data path.

Msg 3110: User does not have permission to RESTORE database 'Coffalyser'

The account with which you are logged in to Windows does not have the required permissions to restore the database. This can happen if you use a custom (non-preconfigured) SQL Server on which strict user rights have been set up. This may also happen if you use a preconfigured SQL Server that was originally installed by another Windows user. If possible, try to execute the command again using a different Windows user account. If this is not possible or not successful, contact us for an alternative way to execute the restore command.