Trouble restoring SQL Database 1.5 TB

[Marcio_Almeida]

Hello, folks!

I'm having a hard time restoring a SQL Database. It's size is about 1.5 TB. The backup was made using an agent script on the client side.

Here's the script configuration for the backups:

OPERATION BACKUP
DATABASE "ECM_DCTM"
SQLHOST "ecmsql"
NBSERVER "ZENON"
STRIPES 4
MAXTRANSFERSIZE 6
BLOCKSIZE 7
NUMBUFS 2
ENDOPER TRUE

And this is what has been used for restores:

OPERATION RESTORE
OBJECTTYPE DATABASE
DATABASE "ECM_DCTM"
# The following image is type: Full
NBIMAGE "ecmsql.MSSQL7.ECMSQL.db.ECM_DCTM.~.7.001of004.20110723102653..C"
SQLHOST "ecmsql"
NBSERVER "ZENON"
STRIPES 004
BROWSECLIENT "ecmsql"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE NOTRECOVERED
NUMBUFS 2
ENDOPER TRUE

OPERATION RESTORE
OBJECTTYPE DATABASE
DUMPOPTION INCREMENTAL
DATABASE "ECM_DCTM"
# The following image is type: Full database differential
NBIMAGE "ecmsql.MSSQL7.ECMSQL.inc.ECM_DCTM.~.7.001of002.20110726050306..C"
SQLHOST "ecmsql"
NBSERVER "ZENON"
STRIPES 002
BROWSECLIENT "ecmsql"
MAXTRANSFERSIZE 6
BLOCKSIZE 7
RESTOREOPTION REPLACE
RECOVEREDSTATE RECOVERED
NUMBUFS 2
ENDOPER TRUE 

Jobs are finalized with this status:

the restore failed to recover the requested files(5)

Here follows the detailed status:

28/07/2011 14:33:47 - started process bptm (20447634)
28/07/2011 14:33:47 - mounting 000145
28/07/2011 14:33:49 - connected
28/07/2011 14:34:12 - mounted; mount time: 00:00:25
28/07/2011 14:34:16 - positioning 000145 to file 132
28/07/2011 14:34:46 - positioned 000145; position time: 00:00:30
28/07/2011 14:34:46 - begin reading
28/07/2011 14:36:02 - positioning 000145 to file 133
28/07/2011 14:36:02 - positioned 000145; position time: 00:00:00
28/07/2011 14:37:06 - positioning 000145 to file 134
28/07/2011 14:37:06 - positioned 000145; position time: 00:00:00
...

28/07/2011 15:33:46 - positioned 000145; position time: 00:00:00
28/07/2011 15:34:49 - positioning 000145 to file 188
28/07/2011 15:34:49 - positioned 000145; position time: 00:00:00
28/07/2011 15:34:53 - begin Restore
28/07/2011 15:34:54 - 1 images required
28/07/2011 15:34:55 - media 000145 required
28/07/2011 15:34:59 - restoring image ecmsql_1311427623
28/07/2011 15:35:10 - requesting resource 000145
28/07/2011 15:35:11 - granted resource 000145
28/07/2011 15:35:11 - granted resource ADIC_Drive_5
28/07/2011 15:35:52 - positioning 000145 to file 189
28/07/2011 15:35:52 - positioned 000145; position time: 00:00:00
28/07/2011 15:36:53 - positioning 000145 to file 190
28/07/2011 15:36:53 - positioned 000145; position time: 00:00:00
28/07/2011 15:37:57 - positioning 000145 to file 191
...

28/07/2011 17:34:32 - positioning 000145 to file 301
28/07/2011 17:34:32 - positioned 000145; position time: 00:00:00
28/07/2011 17:35:36 - positioning 000145 to file 302
28/07/2011 17:35:36 - positioned 000145; position time: 00:00:00
28/07/2011 17:36:33 - Error bpbrm(pid=16973926) socket read failed: errno = 73 - A connection with a remote socket was reset by that socket.
28/07/2011 17:36:33 - end reading; read time: 03:01:47
28/07/2011 18:37:58 - restored image ecmsql_1311427623 - (file read failed(13)); restore time 03:02:59
28/07/2011 18:38:04 - end Restore; elapsed time: 03:03:11
the restore failed to recover the requested files(5)

-=-=-=-=

The log files is attached:

Client:

DBCLIENT

USER_OPS

EVENT_VIEW (both application and system)

Master:

DBCLIENT

EVENT_VIEW (both application and system)

The images of the SQL script manager are attached to.

I used these procedures with no sucess:

DOCUMENTATION: How to configure Veritas NetBackup (tm) for backups and restores of Microsoft SQL Server
http://www.symantec.com/docs/TECH39418

NetBackup for Microsoft SQL Server: Performing a database move to the same SQLHOST or to an alternate SQLHOST
http://www.symantec.com/docs/TECH35995

How to Backup and Restore Microsoft SQL Server Backup via Netbackup
http://www.symantec.com/connect/articles/how-backup-and-restore-microsoft-sql-server-backup-netbacku...

A comprehensive list of solutions for the most common NetBackup for Microsoft SQL Server database agent backup and restore issues
http://www.symantec.com/business/support/index?page=content&id=TECH74475  
 

Any help would appreciated.

Thanks in advance!

[Marianne · Accepted solution]

TECH74475 contains this link :

Restores of large Microsoft SQL server databases using the NetBackup for Microsoft SQL Server database extension fail before jobs start reading data from tape:  http://symantec.com/docs/TECH14997

Extract:

If the NetBackup "Client Read Timeout" for that client is not large enough, the restore processes will have already timed out before the SQL Server requests the first byte of data.

For example, with a 2.4 terabyte SQL database, if the SQL server can write the zeroes at 60MBps, it will take at least 12 hours to zero out the database files. In this example, the Client Read Timeout for the client with the SQL database would need to be set to at least 16 hours, and probably more, to make sure that the SQL database had sufficient time to zero out the files.

 Solution

Increase the value of the NetBackup media server's Client Read Timeout.

The appropriate value for the Client Read Timeout depends on several factors, but the primary factor is the speed at which SQL Server can write data to disk.  One method to determine how fast SQL can zero out the disk would be to use the Performance Monitor. Monitor the disk bytes written to all of the disks used to hold the SQL database and determine the average speed of the writes to disk. Then use this value and the size of the SQL database to determine a reasonable value for the Client Read Timeout.  

In general, the Client Read Timeout value can be set to twice the time required for the backup, and the restore should succeed.  If the backup took two hours, set the Client Read Timeout value to four hours (14400 seconds).  

(PS: Please check the links that you've posted above - they all go to your OWA....)
 

[Oddy]

According to your progress log do you not have enough space on the k:\ drive where you are restoring to.
It says,
 

DBMS MSG - ODBC return code <-1>, SQL State <37000>, SQL Message <3257><[Microsoft][ODBC SQL Server Driver][SQL Server]There is insufficient free space on disk volume 'K:\' to create the database. The database requires 1688207360000 additional free bytes, while only 68853792768 bytes are available.>.

I can see that you are using 4 stripes for the backup and restore scripts, if the backup is multiplexed which is not recommended to use with stripes you can try to change NUMBUFS in the script from 2 to 1 and test the restore again after you have verified that you have enough space on the drive and increased the client read timeout.

If you have firewall involved, make sure that the tcp timeout is increased for the amount of time you estimate the restore to take.

/Oddy