How to troubleshoot NetBackup for Microsoft SQL Server database agent restore issues

Troubleshooting all NetBackup issues requires a certain knowledge of the servers involved with the backup or restore. At a minimum, the following information should be known:

 

The operating system and OS patch level of the master server, along with the NetBackup version and maintenance pack (MP)/ feature pack (FP) level.

The operating system and OS patch level of the media server, along with the NetBackup version and MP/FP level. If the master server is also the media server, this needs to be noted.

The operating system and OS patch level of the client server, along with the NetBackup version and MP/FP level.

 

For database restores, knowledge of the version of the database as well as any patches may also be necessary.

To determine the version of SQL server, open SQL query analyzer, and run the following query:
   select @@version

Review the output, and compare it to the information in Microsoft Q Article 321185 (original number) (     https://learn.microsoft.com/en-US/troubleshoot/sql/general/determine-version-edition-update-level ).

Since SQL restores do not use the tar process, it is necessary to enable different logging than for flat file restore. For SQL restores, enable the   dbclient log file. by creating the directory:

 

<INSTALL_PATH>\veritas\netbackup\logs\dbclient

 

Prepare to gather logs by setting debug levels as follows and re-attempt the operation with logging in place:

Open the Backup, Archive, and Restore interface.

Select File > NetBackup Client Properties.

Click the Troubleshooting tab.

Set the General debug level to 2

Set the Verbose debug level to 5

Set the Database debug level to 9

Click OK to save your changes. 

 

Additionally, it is helpful to review the progress log file from the restore <INSTALL_PATH>\veritas\netbackup\logs\user_ops\mssql\logs

and have access to the SQL error logs, the VDI log, and the Microsoft Application Event Viewer log file.

With this information, there are several important points about SQL restores to remember.  
 

1. All SQL restore operations must be initiated on the client server, from within the NetBackup Microsoft SQL Client GUI. If the database is being restored to an alternate client, the restore must be initiated on the alternate client.

2. To restore to a different server, use an SQL move script - see the Related Documents section below for information on moving SQL databases to alternate servers

3. Assume the time required for a restore is the backup time multiplied by two. If backup took one hour, assume two hours for the restore

For restores of larger databases, it is necessary for the client read timeout to be reset on the media server. See the related article below, on where to set the client read timeout for a SQL restore.

Search the dbbackup / dbclient log file for "<16>," and review the text around these messages, as this frequently indicates the source of the problem.  

ERRORS:

Status Code 25:
This is usually caused by attempting to run the restore from the NetBackup Backup, Archive, and Restore GUI, instead of the NetBackup Microsoft SQL Client GUI. Confirm the restore is being launched from the client using the NetBackup Microsoft SQL Client GUI.

Status Code 41:
If the database being restored is fairly large, and the NetBackup Client Read Timeout on the media server is not set sufficiently high, the restore fails with a "status code 41, connection timed out." Generally, the client read timeout has not been changed from the default of 300 seconds (5 minutes), so the restore runs for 5 minutes, and then fails.  Adjust the client read timeout on the media server to a larger number, and retry the operation. See the related document below, on setting the client read timeout value.  

Status Code 5:
The majority of SQL restores fail with a NetBackup Status Code 5, so it is necessary to review log files to determine the actual source of the error. Enable the dbclient log file (and dbbackup log if 4.5 or earlier) and review these files for anything similar to the following:
 

Note: If SQL Enterprise Manager is open - this can "lock" a database, and prevent NetBackup from performing the restore.  

11:33:47.900 [2684.2368] <16> CODBCaccess::LogODBCerr: DBMS MSG - ODBC message. ODBC return code <-x>, SQL State <37000>, Message Text <[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be obtained because the database is in use.>
 

If the logical name is modified (name above the "MOVE" line in a script):

INFO ODBC message. ODBC return code <-x>, SQL State <37000>, Message Text <[Microsoft][ODBC SQL Server Driver][SQL Server]Logical file 'file_name' is not part of database 'database_restore'. Use RESTORE FILELISTONLY to list the logical file names.>
INFO <[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE is terminating abnormally.>
 

When modifying MOVE scripts, change ONLY the lines which are commented out with the hash mark (#) and not any of the other lines.  Also, please note that failing to change the name of the database (on the DATABASE line) could potentially overwrite production data and result in lost information.  Please consult with the SQL DBA to confirm the information in the MOVE script is correct.  

 

INFO ODBC message. ODBC return code <-x>, SQL State <37000>, Message Text <[Microsoft][ODBC SQL Server Driver][SQL Server]Device activation error. The physical file name 'D:\mssql2k\MSSQL\data\database_restore.mdf' may be incorrect.>
 

11:04:32 [4900,640] <16>  CODBCaccess::LogODBCerr: DMBS MSG - ODBC error. ODBC return code <-x>, SQL State <37000>, Message Text <[Microsoft][ODBC SQL Server Driver][SQL Server]There is insufficient free space on the disk volume 'C:\' to create the database. The database requires 360815001300 additional free bytes, while only 327193161728 bytes are available.>