Restore problems using Veritas NetBackup

Question

Post reply

Restore problems using Veritas NetBackup

Anthony Stanley

SSC Eights!

Points: 983
More actions
June 9, 2005 at 2:33 am

#164525

Hi,
Got a question with regards to restoring a database using NetBackup. Had a situation yesterday doing a restore of a backup from a production server onto a development box.
Did the restore using a batch defined in the NetBackup MS SQL Agent. The database is approximately 170Gb, using 6 data files and 2 log files. The production box and the development box are not set up the same. The production box has one more drive than the dev box, one of the log files is stored on this extra drive so when doing the restore a move option has to be specified. This procedure happens twice a month.
For some odd reason the location off the .mdf file was changed from the last restore on the dev box, ie last time this procedure was done the .mdf file on the dev box was on the E: when we came around around to do it this time we found it on the D:. We are assuming one of the developers moved the location of the mdf file, why we do not know?. Normally the location of all the files are exactly as the production box, except for the location of one log file, a move option is specified in the restore statement to sort this out. That fact that the .mdf file was not in the right location did not seem like a big issue, we just specified an extra move statement to get it where it should be (ie. E: not D.
Everything seemed to running fine until 4 hours after the restore had started NetBackup threw up a series of ODBC errors, and we suddenly had a database that was not working. What appeared to have happened is that NetBackup had restored the physical files, but when it tried to run the T-SQL restore statement there were errors.
This is a procedure that happens regularly and hasn't given a problem since it was setup. The only change we made to the regular procedure was to specify an additional move to sort out the location of the mdf file. After trying multiple things to sort out the restore (trying to attach the physical files, rebuilding logs, etc...) without rerunning it (as this would take hours!) we gave up and reran the entire restore procedure. Before rerunning the restore procedure we deleted the database from the dev box, including all the physical files, and setup it up again (without any data) with the exact structure that the dev box was supposed have had (ie with the correct location for the mdf). We ran the restore with the orginal settings (ie only one move to sort out the location of the the log) and everything went through as it was supposed to!!
Does anyone know why this happened?? The only difference between the 2 jobs was an extra restore!
Here are the 2 Veritas NetBackup batches used :
Unsuccessful!!
OPERATION RESTORE
OBJECTTYPE DATABASE
DATABASE "MDDQA"
MOVE "Mdd3"
TO "F:\MSSQL\Data\MDD3_log2.ldf"
MOVE "AIO_NEw"
TO "E:\MSSQL\Data\Mdd3.mdf"
NBIMAGE "cpxjeprod.MSSQL7.CPXJEPROD.db.MDD3.~.0.001of001.20050607220844..C"
SQLHOST "mdskzuml-dev"
#SQLINSTANCE "Default"
NBSERVER "xfbackup"
BROWSECLIENT "cpxjeprod"
MAXTRANSFERSIZE 0
BLOCKSIZE 0
RESTOREOPTION REPLACE
RECOVEREDSTATE RECOVERED
ENDOPER TRUE
Successful!
OPERATION RESTORE
OBJECTTYPE DATABASE
DATABASE "MDDQA"
MOVE "Mdd3"
TO "F:\MSSQL\Data\MDD3_log2.ldf"
NBIMAGE "cpxjeprod.MSSQL7.CPXJEPROD.db.MDD3.~.0.001of001.20050607220844..C"
SQLHOST "mdskzuml-dev"
#SQLINSTANCE "Default"
NBSERVER "xfbackup"
BROWSECLIENT "cpxjeprod"
MAXTRANSFERSIZE 0
BLOCKSIZE 0
RESTOREOPTION REPLACE
RECOVEREDSTATE RECOVERED
ENDOPER TRUE
when the first batch didn't go through it returned a sql state error 37000, this is a syntax/authorisation error. I don't think the problem would be authorisation as the same user was used both times.
Here is the SQL statement Netbackup used to restore the database on the unsuccessful attempt.
"restore database "MDDQA" from VIRTUAL_DEVICE='VNBU0-4716-3976' with blocksize = 512, maxtransfersize = 65536, buffercount = 1, replace , recovery, move 'Mdd3' to 'F:\MSSQL\Data\MDD3_log2.ldf', move 'AIO_NEw' to 'E:\MSSQL\Data\Mdd3.mdf', replace"
Netbackup doesn't show the SQL statement if the restore is successful.
While we have solved the problem, I would like to get some feedback as why this could have happend and how it could be avoided.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Gray SSChampion Points: 10667 More actions · Answer 1

Anthony,

Can you post the error messages from NetBackup logs? also what version are you using?

I might be able to help.

Anthony Stanley SSC Eights! Points: 983 More actions · Answer 2

Here's the full log for the failed restore:

#***************************************************************************

#

OPERATION RESTORE

OBJECTTYPE DATABASE

DATABASE "MDDQA"

MOVE "Mdd3"

TO "F:\MSSQL\Data\MDD3_log2.ldf"

MOVE "AIO_NEw"

TO "E:\MSSQL\Data\Mdd3.mdf"

NBIMAGE "cpxjeprod.MSSQL7.CPXJEPROD.db.MDD3.~.0.001of001.20050607220844..C"

SQLHOST "mdskzuml-dev"

#SQLINSTANCE "Default"

NBSERVER "xfbackup"

BROWSECLIENT "cpxjeprod"

MAXTRANSFERSIZE 0

BLOCKSIZE 0

RESTOREOPTION REPLACE

RECOVEREDSTATE RECOVERED

ENDOPER TRUE

INF - LOAD STARTED USING

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)

Dec 17 2002 14:22:05

Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Batch = C:\Program Files\VERITAS\NetBackup\DbExt\MsSql\rest.bch, Op# = 1.

INF - Using backup image 'cpxjeprod.MSSQL7.CPXJEPROD.db.MDD3.~.0.001of001.20050607220844..C'.

INF - restore database "MDDQA" from VIRTUAL_DEVICE='VNBU0-4716-3976' with blocksize = 512, maxtransfersize = 65536, buffercount = 1, replace , recovery, move 'Mdd3' to 'F:\MSSQL\Data\MDD3_log2.ldf', move 'AIO_NEw' to 'E:\MSSQL\Data\Mdd3.mdf', replace

INF - #Stripes: 1, #Bufs per stripe 1, From: NetBackup on xfbackup

INF - Created VDI object for SQL Server instance <*DEFAULT*>. Connection timeout is <300> seconds.

Restore started Wed Jun 08 10:57:06 2005

10:57:08 (36795.xxx) Restore job id 36795 will require 1 image.

10:57:08 (36795.xxx) Media id K582L1 is needed for the restore.

10:57:15 (36795.001) Restoring from image created Tue Jun 07 23:04:20 2005

10:57:17 (36795.001) INF - Data socket = xfbackup.4878

10:57:17 (36795.001) INF - Waiting for positioning of media id K582L1 on server xfbackup for reading.

10:57:18 (36795.001) INF - Name socket = xfbackup.4800

10:57:18 (36795.001) INF - Job id = 36795

10:57:18 (36795.001) INF - Backup id = cpxjeprod_1118178260

10:57:18 (36795.001) INF - Backup time = 1118178260

10:57:18 (36795.001) INF - Policy name = cpxjeprod_db_daily

10:57:18 (36795.001) INF - Snapshot = 0

10:57:18 (36795.001) INF - Frozen image = 0

10:57:18 (36795.001) INF - Backup copy = 0

10:57:18 (36795.001) INF - Master server = xfbackup

10:57:18 (36795.001) INF - Media server = xfbackup

10:57:19 (36795.001) INF - New data socket = xfbackup.4879

10:57:19 (36795.001) INF - Use shared memory = 0

10:57:19 (36795.001) INF - Restore id = 36795.001

10:57:19 (36795.001) INF - Encrypt = 0

10:57:19 (36795.001) INF - Client read timeout = 14400

10:57:19 (36795.001) INF - Media mount timeout = 0

10:57:19 (36795.001) INF - client = mdskzuml-dev

10:57:19 (36795.001) INF - requesting_client = mdskzuml-dev

10:57:19 (36795.001) INF - browse_client = cpxjeprod

10:58:24 (36795.001) INF - Beginning restore from server xfbackup to client mdskzuml-dev.

15:03:05 /cpxjeprod.MSSQL7.CPXJEPROD.db.MDD3.~.0.001of001.20050607220844..C

DBMS MSG - ODBC message. ODBC return code <-1>, SQL State <37000>, Message Text <[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot associate files with different databases.>

DBMS MSG - <[Microsoft][ODBC SQL Server Driver][SQL Server]Device activation error. The physical file name 'E:\MSSQL\Data\FileCausalFactor_Data.ndf' may be incorrect.>

DBMS MSG - <[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot associate files with different databases.>

DBMS MSG - <[Microsoft][ODBC SQL Server Driver][SQL Server]Device activation error. The physical file name 'E:\MSSQL\Data\mddprimaryix.ndf' may be incorrect.>

DBMS MSG - <[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot associate files with different databases.>

DBMS MSG - <[Microsoft][ODBC SQL Server Driver][SQL Server]Device activation error. The physical file name 'E:\MSSQL\Data\FileSalesHistory_Data.ndf' may be incorrect.>

DBMS MSG - <[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot associate files with different databases.>

DBMS MSG - <[Microsoft][ODBC SQL Server Driver][SQL Server]Device activation error. The physical file name 'd:\MSSQL\Data\FileForecast_Data.ndf' may be incorrect.>

DBMS MSG - <[Microsoft][ODBC SQL Server Driver][SQL Server]Processed 3325072 pages for database 'MDDQA', file 'AIO_NEw' on file 1.>

ERR - Error found executing <restore database "MDDQA" from VIRTUAL_DEVICE='VNBU0-4716-3976' with blocksize = 512, maxtransfersize = 65536, buffercount = 1, replace , recovery, move 'Mdd3' to 'F:\MSSQL\Data\MDD3_log2.ldf', move 'AIO_NEw' to 'E:\MSSQL\Data\Mdd3.mdf', replace>

ERR - Error in GetCommand : 0x80770004. The text follows:

CONTINUATION: - An abort request is preventing anything except termination actions.

15:03:08 (36795.001) Status of restore from image created Tue Jun 07 23:04:20 2005 = the requested operation was successfully completed

15:03:09 INF - Server status = 0

Not 100% sure what version of NetBackup we're using, the backup batch was called using the NetBackup MS SQL Agent. Will have to confirm with our backup operators as to what version we're running, I'm pretty sure its the latest version, we're running it on a Sun box with a robot.

All the errors returned weren't NetBackup errors they were ODBC errors returning a SQL State of 37000, at least that was the first error, then a series of SQL errors came back after that.

My guess is that NetBackup restored the images correctly but passed the wrong SQL statement to MSSQL to do the recovery. But that is only a guess.

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

This is interesting. While I have had success with NetBackup renaming a physical file during a restore, I've never tried to specify a different folder for the file. I do mean different than the one that already existed in the target server, regardless of the locations of the source database files.

I would have to assume that you are correct about the recovery commands being out of whack.

Did it actually move the mdf or do you have the old one from before the restore in the location that one of the dev's move it to?

jg

Anthony Stanley SSC Eights! Points: 983 More actions · Answer 4

It moved the mdf correctly. In fact it restored all the files as they should have been. The file sizes were the correct size when compared to the backup, and the files were in the correct places as specified in the move statement. So in theory it should have worked.

Like I said earlier, I think that Netbackup screwed up the SQL statement when 2 moves were specified. I would like someone to confirm the syntax on the SQL statement to see if it is correct or not. There are 2 replace statements specified on the SQL restore command that failed, and I'm assuming this is incorrect, but BOL is not very helpful in this regard.

Here's the SQL statement that failed (as shown in previous posts).

"restore database "MDDQA" from VIRTUAL_DEVICE='VNBU0-4716-3976' with blocksize = 512, maxtransfersize = 65536, buffercount = 1, replace , recovery, move 'Mdd3' to 'F:\MSSQL\Data\MDD3_log2.ldf', move 'AIO_NEw' to 'E:\MSSQL\Data\Mdd3.mdf', replace"

Jeff Gray SSChampion Points: 10667 More actions · Answer 5

Anthony,

I've recently discovered that the REPLACE statements are likely not the cause of the error. I successfully restored a database with multiple MOVEs and REPLACEs.

I can't really see anything that would cause a problem like you saw, other than Netbackup issuing the wrong command, like you believe.

jg