How to restore SQL databases when SQLINSTANCE was specified in lower case
Problem
Cannot restore SQL databases when the SQLINSTANCE value has been defined in lower case inside the backup script. (Using an UNIX master).
Error Messages
"Err - Error in VxBSAQueryObject: 17 Continuation: No object matched the specified predicate."
Event Type: Error Event Source: MSSQLSERVER Event Category: (2) Event ID: 18210 Date: 21/10/2013 Time: 23:38:43 User: DOMAIN\Administrator Computer: PhysicalSERVER Description: BackupMedium::ReportIoError: read failure on backup device 'VNBU0-2084-484-12683XXX17'. Operating system error 995(error not found).
Troubleshooting
SQL database restores failed with the following error inmediately:
"Err - Error in VxBSAQueryObject: 17 Continuation: No object matched the specified predicate."
Other databases in the same server could be restored succesfully, and there was plenty of disk space in the server.
The directory <install_path>\NetBackup\db\altnames exists and inside the directory the file No.Restrictions was present.
The Media Server was available and defined as a valid restore server.
After reviewing the restore batch file:
#
# This is a template for the database MOVE command.
#
OPERATION RESTORE
OBJECTTYPE DATABASE
RESTORETYPE MOVE
DATABASE "dbname_restore"
MOVE " dbname_Data"
TO "P:\DATA2\dbname_restore.mdf"
MOVE "dbname_Log"
TO "P:\LOG\dbname_1_restore.ldf"
#
# The following image is type: Full
NBIMAGE "PHYSICALSERVER.MSSQL7.SQLSERVER\INSTANCENAME.db.dbname.~.7.001of001.20130929234153..C"
SQLHOST "SQLSERVER"
SQLINSTANCE "INSTANCENAME"
NBSERVER "NETBACKUP"
BROWSECLIENT "SQLSERVER"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RECOVEREDSTATE RECOVERED
NUMBUFS 1
ENDOPER TRUE
the restore operation was launched again after correcting the script as suggested in:
www.symantec.com/business/support/index?page=content&id=TECH63773
According to this BROWSECLIENT value was modified to lower case as the client name was specified in Netbackup policy:
NBIMAGE "PHYSICALSERVER.MSSQL7.SQLSERVER\INSTANCENAME.db.dbname.~.7.001of001.20130929234153..C"
SQLHOST "SQLSERVER"
SQLINSTANCE "INSTANCENAME"
NBSERVER "NETBACKUP"
BROWSECLIENT "sqlserver"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RECOVEREDSTATE RECOVERED
NUMBUFS 1
ENDOPER TRUE
It failed again because BROWSECLIENT and SQLHOST values can be specified either in low or upper case, it doesn´t matter, NetBackup uses both combinations as stated in the following articles:
http://www.symantec.com/business/support/index?page=content&id=HOWTO69710
“NetBackup tries to retrieve the backup images by specifying the client name with all upper case characters or all lower case characters.”
Basically it would be a problem if you have mixed characters but it was not the case.
We realized that backup scripts of the databases whose restores were working were specified in the following way:
OPERATION BACKUP
DATABASE “dbname”
SQLINSTANCE "INSTANCENAME"
NBSERVER "NETBACKUP"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RECOVEREDSTATE RECOVERED
NUMBUFS 1
While failing ones were like:
OPERATION BACKUP
DATABASE “dbname”
SQLINSTANCE "instancename"
NBSERVER "NETBACKUP"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RECOVEREDSTATE RECOVERED
NUMBUFS 1
After modifying the backup script to upper case, restores started to work.
The issue then, was to restore the old backup images which were saved with a bad sql instance definition.
Solution
After replacing the NBIMAGE characters referencing the sql instance in the restore script the operation was completed succesfully. So the script should be like:
RESTORETYPE MOVE
DATABASE "dbname_restore"
MOVE " dbname_Data"
TO "P:\DATA2\dbname_restore.mdf"
MOVE "dbname_Log"
TO "P:\LOG\dbname_1_restore.ldf"
#
# The following image is type: Full
NBIMAGE "PHYSICALSERVER.MSSQL7.SQLSERVER\instancename.db.dbname.~.7.001of001.20130929234153..C"
SQLHOST "SQLSERVER"
SQLINSTANCE "INSTANCENAME"
NBSERVER "NETBACKUP"
BROWSECLIENT "sqlserver"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RECOVEREDSTATE RECOVERED
NUMBUFS 1
ENDOPER TRUE