Backup Exec 10.x Microsoft SQL backups that have VSS for Advanced Open File Option (AOFO) enabled may not protect a database file due to an invalid path stored within SQL.

Details:

The SQL issue described in this article is only encountered when the following conditions are met:

Microsoft SQL 2000 or SQL 2005 is being backed up by Backup Exec 10.x for Windows Servers.
The backup job reports that it is successful.
Advanced Open File Option (AOFO) is enabled for the backup job.
'Automatically select open file technology' or 'Microsoft Volume Shadow Copy Service' is selected as the Advanced Open File Option configuration. (Figure 1)
The Microsoft SQL database being backed up was created manually by pointing to a directory path with superfluous additional backslashes (e.g. F:\mydb\data\\sales.mdf)

Figure 1

The SQL backup issue occurs due to an unexpected path being passed to Backup Exec. As a result, any database files that are being referenced by Microsoft SQL in this unusual way will be skipped but the backup will still report as successful. The byte count in the backup job will report the actual amount of data being backed up, so if all the database files are referenced in this manner then the byte count would be zero.

Permanent Fix

This issue has been addressed in the following Backup Exec Hotfixes:

Hot Fix 37 for Backup Exec 10.0.5484
http://support.veritas.com/docs/285055

Hot Fix 33 for Backup Exec 10.0.5520
http://support.veritas.com/docs/285059

Hot Fix 37 for Backup Exec 10.1.5629
http://support.veritas.com/docs/285069

Note: These hotfixes will cause the backup job to fail if it runs into an invalid SQL database path. The workaround below will still need to be performed in order to correct this issue.

Workaround

Symantec strongly recommends that all customers performing SQL backups verify whether any databases have been created manually with an accidental repeated backslash.

For SQL 2000:

Open Enterprise Manager, and look in the properties of each database and see if any of the files (logs included) point to a directory path with multiple backslashes that are back to back (e.g. c:\mydb\\data\sales.mdf).

Figure 2 and 3 show where to look within the properties of the database, and Figure 2 shows what an incorrect path would look like. If Enterprise Manager is not installed on the server, or is not accessible, following the directions under the SQL 2005 section can be used to determine if any databases have any additional backslashes and need to be corrected.

Figure 2

Figure 3

For SQL 2005:

Since SQL 2005 masks the repeated backslashes in the SQL user interface while still storing the inaccurate path in the database configuration table, it will be necessary to run the query below on the SQL server from a command prompt:

osql -S127.0.0.1 -E -d<database name> -Q"select * from sysfiles"

Run this query for each database in use and substitute the database name for '<database name>' in the query.

Note: If a 'bad' path is found to be stored for one of the SQL databases, then the first action should be to insure a complete backup. Therefore before correcting the database path, each problem database should be backed up in a separate backup job that does not have AOFO enabled. Afterwards, the byte count should be verified against expected outcomes. Once this is done, the database path stored within SQL can be corrected by following the procedure outlined in the Microsoft KB article below:

http://support.microsoft.com/kb/224071/

Acknowledgements
Microsoft KB Article 224071

Products Applied:
Backup Exec 10.0, 10.0 5484, 10.0 5484 SP1, 10.0 5484 SP2, 10.0 5484 SP3, 10.0 5484 SP4, 10.0 5520, 10.0 5520 SP1, 10.0 5520 SP2, 10d (10.1), 10d (10.1) 5629, 10d (10.1) 5629 SP1

Last Updated: January 14 2010 02:15 PM GMT
Expires on: 365 days from publish date

Subscribe to receive critical updates about this document

Subjects:
Backup Exec
   Agent For Sql: Backup-Restore, Troubleshoot
   Application: Open File Option, Sql Module
   Publishing Status: Techalert

Languages:
Russian, English (US), French, German, Spanish, Italian, Japanese, Chinese, Korean

Operating Systems:
Windows Server 2003

DataCenter, DataCenter (IA64), DataCenter (x64), DataCenter SP1, DataCenter SP1(IA64), DataCenter SP1(x64), Enterprise (IA64), Enterprise (x64), Enterprise SP1(IA64), Enterprise SP1(x64), Enterprise Server, Enterprise ServerSP1, R2, Standard Server, Standard Server SP1, Standard Server(x64), Storage Server, Storage Server SP1, Web Server, Web Server SP1

Windows Small Business Server 2003

Premium Edition, Standard Edition

Symantec World Headquarters:
20330 Stevens Creek Blvd. Cupertino, CA 95014
World Wide Web: http://www.symantec.com,
Tech Support Web: http://entsupport.symantec.com,
E-Mail Support: http://seer.entsupport.symantec.com/email_forms,
FTP: ftp://ftp.entsupport.symantec.com or http://ftp.entsupport.symantec.com

THE INFORMATION PROVIDED IN THE SYMANTEC SOFTWARE KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. SYMANTEC SOFTWARE DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL SYMANTEC SOFTWARE OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES,EVEN IF SYMANTEC SOFTWARE OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.