How to troubleshoot Microsoft SQL Server database backup issues

Problem

How to troubleshoot Microsoft SQL Server database backup issues

Solution

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) level
• The operating system and OS patch level of the media server, along with the NetBackup version and MP 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 level
• For database backups and restores, knowledge of the version of the database as well as any patches is also necessary.

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

Review the output and compare it to the information in Microsoft Q321185
https://support.microsoft.com/en-gb/help/321185/how-to-determine-the-version-edition-and-update-level-of-sql-server-an

Since SQL backups and restores do not use the bpbkar process, it is necessary to enable different logging than for flat file backup.  For SQL backups and restores, enable the dbclient log file by creating a folder called dbclient in the <install_path>\veritas\netbackup\logs directory.  Additionally, it is helpful to review the progress log file from the backup or 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.

In addition, there are two Windows services which must be running to correctly configure the NetBackup MS SQL GUI.  They are the Protected Storage and Remote Registry Service.  Confirm these services are running if there are difficulties configuring the NetBackup SQL GUI.

Most SQL backups fail with a Status 2, none of the requested files were backed up.  Here are some possible causes of this failure:

1. For SQL backups to run from the master server, the NetBackup Client Service needs to be started as an account with specific rights.  For more information on SQL permissions, please review the following article:         

https://www.veritas.com/support/en_US/article.100017184.html

2. Confirm the SQL databases can be seen in the NetBackup MS SQL Client.  If not, the user set in the NBU SQL GUI for backup and restores is not an administrator.  From the Options menu, select Set DBMS login parameters, and enter an administrator ID and password.  Review the following article for required permission information:  

https://www.veritas.com/support/en_US/article.100017184.html

3. After confirming the database appears in the GUI, attempt a backup of a test database from the client GUI.  From the Actions menu, select backup, and do an immediate backup of a small database (such as Model).  If this fails, review the dbclient log files for errors.

4. If backups from the client are successful, delete and recreate the batch file (.bch) being used for backups.  Do not attempt to overwrite any existing batch files, as saving a batch file to the same name as an existing batch file appends any newly specified operations to the existing batch file.  From Actions > Backup, select the names of the databases for backup on the right side of the window, and then select "Save for later execution".  After clicking on the OK button, enter a name for the batch file, and save the script.  It is not necessary to open this script if prompted. Note, if the script is saved in the default location (<install_path>\veritas\netbackup\dbext\mssql), only the name of the batch file needs to be entered in the SQL policy "Backup Selections" tab.  If the batch file is placed in a location other than the default, the entire path name and the name of the batch file needs to be entered in the "Backup Selections" tab.  

If none of this applies, collect the following information and log files, and contact NetBackup Technical Support:

• The dbclient log file ( <install_path\veritas\netbackup\logs\dbclient\######.log)
• The progress log ( <install_path\veritas\netbackup\logs\user_ops\mssql\logs)
• The SQL error log (named ERRORLOG, and usually located in the <install_path>\mssql\log directory)
• The Microsoft Application Event Viewer log file in text format.  To collect this log file in the correct format:

1. Click on Start > Settings > Control Panel > Administrative Tools > Event Viewer
2. Click on "Application" in the left hand pane
3. From the Action menu, select "Save Log File As..."
4. Under "File name", specify "application.txt", and under "Save as type", select "Text (Tab delimited (*.txt)"