Source:Troubleshooting MS SQL

Other languages:

Deutsch
English

Microsoft SQL Server

Incorrect login

Problem

If a backup or restore ends with errors and you find the following information in the log files, an attempt has been made to address a SQL Server instance on a client that is not entered locally on this system. The selected Trusteed Connection only allows the registration on a SQL Server where the instance is locally active:

DB Module: [DB-Library: Login failed for user '(null)'. 
Reason: Not associated with a trusted SQL Server connection.]
DB Module: [DB-Library message: Login incorrect.]

⇒ Solution

The backup client or end node on the active SQL Server must be changed.

Microsoft SQL Server backup failure

Problem

Backup fails with the message "The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "<database>" under the current security context."

Possible causes

The user SYSTEM is not allowed to connect to the MS-SQL database because the SEP sesam daemon runs as the user SYSTEM by default.

⇒ Solution

Change the SEP sesam daemon to a user account that has permission to connect to the database(s). Open the Windows service management console on your MS-SQL server, edit the properties of the SEP sesam service, switch to Log on tab and use valid credentials, then restart the SEP sesam daemon.

MS SQL Server backup shows message "The Transaction Log For Database Is Full"

Problem

During backup of MicroSoft SQL Server the backup log can show the message: "The transaction log for database '<DATABASE>' is full due to 'LOG_BACKUP'".

Possible causes

This error message indicates that the LOG area is full. This can happen when no INC backup are made, only FULL backups.

⇒ Solution

Increase the size of the LOG area and perform a FULL and INC backup so that the LOGs can be truncated correctly. Then, set up regular INC backups so that the LOG area does not fill up again.

For more information on resolving this error see here.

Restoring a database from AlwaysOn Availability Group fails with RESTORE cannot operate on database 'Test1' because it is configured for database mirroring or has joined an availability group

	Note
	MS AlwaysOn is only supported by SEP sesam with the assistance of SEP sesam support.

Problem

The MS SQL AlwaysOn Availability Group database restore fails with an error:

DB Module: [UNKNOWN error: 4294967295 -. Got error: [SQL Server]RESTORE cannot operate on database 'Test1' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.]

Cause

It is not possible to restore the MS SQL database which is part of AlwaysOn Availability Group.

⇒Solution

To restore a database which is part of AlwaysOn Availability Group successfully, proceed as follows:

Remove the database from the AlwaysOn Availability Group.
Restore the database to the primary replica in the AlwaysOn Availability Group.
Add the restored database to the AlwaysOn Availability Group again by using Full as a data synchronization option.

Afterwards the database will be replicated to the secondary replicas.

Restore fails with The system cannot find the path specified

Problem

If the original database was removed or the database should be restored to a different location, then the path for the database must be created before the restore. If the path for the database does not exist, the following message appears:

Directory lookup for the file "C:\Programme\Microsoft SQL 
Server\MSSQL$ZWEITE_DB\data\sesamdb_Data.MDF" 
failed with the operating system error 3(The system cannot find the path specified.).
File 'sesamdb_Data' cannot be restored to 'C:\Programme\Microsoft SQL 
Server\MSSQL$ZWEITE_DB\data\sesamdb_Data.MDF'. 
Use WITH MOVE to identify a valid location for the file.

Cause

The system cannot find the database path if the original database was removed or the database path wasn't created before restoring to a different location.

⇒Solution

In this case you have to restore the database to a new location, as described in Restoring a database to a new location by using GUI relocation or move option.

Restore fails with Directory lookup for the file '...' failed...

Problem

You receive the following warning:

DB Module: [DB-Library: Directory lookup for the file "e:\Database\SQL Server 2000 SE
\MSSQL\Data\sesam.mdf"failed with the operating system error 21(The device is not ready.).]
DB Module: [DB-Library: File 'sesam_db' cannot be restored to 'e:\Database\SQL Server 2000 SE
\MSSQL\Data\sesam.mdf'.
  Use WITH MOVE to identify a valid location for the file.]

Possible causes

The existing path where the database files were located is not on the target system or an entry was made in the "Move" option that used a non-existing path name.

⇒ Solution

A path name must be created or the correct path must be entered in the Move option. Entering the correct path name is the easier solution. Using a long path name in the Move option may cause problems, in which case the input can be shortened.

Restore failure due to SQL Server connection

Problem

The restore fails with the following error:

DB Module: [DB-Library message: Unable to connect: SQL Server is unavailable or does not exist.  
  Unable to connect: SQL Server does not exist or network access denied.; Net-Library message: ConnectionOpen (Connect()).; ]

Possible causes

The selected server does not exist. It is possible that an instance was incorrectly entered without the server name.

⇒ Solution

Check the server name. If necessary, enter the restore target fully qualified as follows:

<HOSTNAME>/<Instance>/<DB Name>

Restore with Move option fails

Problem 1

Restore using the Move option fails with the warning The physical file name '...' may be incorrect. The following error occurs:

DB Module: [DB-Library: A file activation error occurred. The physical file name 
'c:/temp/sesam_log.ldf' may be incorrect.
  Diagnose and correct additional errors, and retry the operation.]
DB Module: [DB-Library: File 'sesam_db_log' cannot be restored to 'c:/temp/sesam_log.ldf'.
  Use WITH MOVE to identify a valid location for the file.]

Possible causes

The wrong syntax was used in the file name, e.g., / instead of \.

⇒ Solution

Enter the path with the correct syntax.

	Note
	If the SEP sesam Server is using a Postgres database, e.g., with Linux x64, then the character '\' must be entered twice '\\', otherwise it will disappear. For example: `-a move=Mgmt_data:"e:\\SQL Server 2000 SE\\MSSQL\\Data\\Mgmt.mdf" -a move=Mgmt_log:"e:\\SQL Server 2000 SE\\MSSQL\\Data\\Mgmt_log.ldf"`

Problem 2

Restore with Move option fails with Logical file '...' is not part of database. The following error occurs:

DB Module: [DB-Library: Logical file 'Mgmt_data' is not part of database 'sesam_db2'.
  Use RESTORE FILELISTONLY to list the logical file names.]

Possible causes

The wrong logical name was entered in the Move option.

⇒ Solution

Enter the logical filename correctly. This can be found in the backup log file (NOT-file). For example, in the backup log file, you see the following lines:

DB Module: [DB-Library: Processed 256 pages for database 'sesam_db', file 'sesam_db' on file 1.]
DB Module: [DB-Library: Processed 1 pages for database 'sesam_db', file 'sesam_db_log' on file 1.]

The logical file names in this case are sesam_db and sesam_db_log. These should be entered in the Move option.

Restored database remains in the state Restoring

Problem

A database remains in state Restoring... after the restore finishes.

Possible causes

This happens if the option Auto recover was not selected in the restore wizard.

⇒ Solution

Select option the option Auto recover in the restore wizard or execute sbc in the CLI with the option -a recover for the particular database. For example:

Call the sbc in the command line with:

sbc -r -a recover sbcmsql:"/MIRACULIX/SECOND/msdb"

	Note
	During the restore of a database with additional transaction log files (generation restore), the database remains in the state "Restoring..." until the last restore (with the option `-a recover`) ends.

Disaster recovery fails with ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access denied.

Problem

The MS SQL disaster recovery fails with an error:

ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access denied.

Cause

This error typically indicates either network or installation-related issues; the SQL Server does not exist, is not available or is not found.

⇒Solution 1:

If possible, restore the system databases from the latest path backup. If no such backup exists, check solutions 2 and 3.

⇒Solution 2:

Create a new MS SQL instance. For this, you will need the MS SQL install files. For details, see How to manually create the Microsoft SQL Server 2014 ACT7 Instance. Then restore the MS SQL databases to the new instance with the target path <server_name>/<instance_name>.

⇒Solution 3:

You can also rebulid MS SQL system databases. The procedure differs slightly if you rebuild the databases from CD or DVD drive (MS SQL Server 2005) or from the installation folder on your local computer in \Binn\Templates\ (MS SQL Server ≥ 2008), for example, C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Templates. You have to run the setup.exe command from the command prompt, then proceed as follows:

Click Start -> Run, type cmd, and click OK.
Depending on your MS SQL version, run the relevant command to rebuild the system databases:

To rebuild the system databases from CD or DVD drive (MS SQL Server 2005), run the following command:

 start /wait <CD_or_DVD_drive>\setup.exe /qn INSTANCENAME=<instance_name> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<strong_password>

For example:

 start /wait D:\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=p@ssw0rd

For details, see How to Rebuild System Databases in SQL Server 2005.

To rebuild the system databases from \Binn\Templates\ (MS SQL Server ≥ 2008), run the following command:

 setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=<instance_name> /SQLSYSADMINACCOUNTS=<accounts> /SAPWD=<strong_password>

For details and troubleshooting, see How to Rebuild System Databases in SQL Server 2008.

Any form of reproduction of the contents or parts of this manual is allowed only with the express written permission from SEP AG. When compiling and designing user documentation SEP AG uses great diligence and attempts to deliver accurate and correct information. However, SEP AG cannot issue a guarantee for the contents of this manual.