How to restore SQL databases when SQLINSTANCE was ...

NBdmecha · ‎10-22-2013

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&pmv=print&impressions=&viewlocale=&id=HO...

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

Backup & Recovery