Auteur de questions
Unable to restore databases from tape: Timeout expired. The timeout period elapsed prior to completion of the operation or the
I'm trying to test my backups by restoring from production onto a DEV machine.
However, when I insert the backup tape into the drive, then go into SSMS and right click on
Databases -> Restore Database -> From Device .... -> Tape -> Add -> (my tape drive \\.\Tape0) -> Contents
it displays the contents as it should.
So I click "Close" to close the contents and bring me back to the Specify Backup where I have \\.\Tape0 selected and I then click "OK"
SSMS sits for a few seconds, and then I get a time out error message:
TITLE: Microsoft SQL Server Management Studio
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)
That's all the further I can get... any suggestions?
I'm VERY perplexed by this...
Toutes les réponses
Ok, it looks like this is happening:
It is setting
SET LOCK_TIMEOUT 10000
Which is only 10 seconds... then it goes and does a few things before it gets to:
RESTORE HEADERONLY FROM TAPE = N'\\.\Tape0' WITH NOUNLOAD
Which runs for 40 seconds (longer than 10 and I assume that to be the cause of the error).
Anyway to adjust the time that SSMS uses for the lock_timeout? (Assuming that is what is causing the time out)?
The restore headeronly command runs for 20 seconds before the error returns.
However, I can't locate any way to tell SSMS to wait longer than 20 seconds, or to adjust the wait period in any way.
I never was able to fix my issue.
I just stopped using the tape drive (I think it was just a crappy drive an HP DAT 72 drive and it acted flaky all the time).
My production server had the same model of drive and would occasionally have similar problems.
Stopped using that as well, backed up to the network instead and had a different server push those to tape.
The real killed with going straight to tape was that even if the drive worked it took SO LONG so my backup process would last "forever" instead of just a minute or two when I backup to a network location.
I had messed around with restore header only, issuing a tape rewind command, etc... just in the hopes of getting the tape ready BEFORE the backup ran so it would respond more quickly, and that USUALLY worked, but still... huge pain sadly.
Can you apply this fix and then try the restore:
967205 FIX: A time-out occurs when you use SQL Server Management Studio to try to restore a large database from a backup on a tape in SQL Server 2008
To work around the issue, we can use the below trace flags with commands to restore the large databases.
For the restore:
DBCC TRACEON ( 3228 , 3605 , -1 )
DBCC TRACEON ( 3213 , 3605 , -1 )
DBCC TRACEON ( 3028 , 3605 , -1 )
RESTORE DATABASE <Database name>
FROM TAPE = '\\.\tape0'
STATS = 1
DBCC TRACEOFF ( 3228 , 3605 , -1 )
DBCC TRACEOFF ( 3213 , 3605 , -1 )
DBCC TRACEOFF ( 3028 , 3605 , -1 )
- Proposé comme réponse Chris 4ward mercredi 7 juillet 2010 14:50
The following blog post talks about this issue:
If you are using SSMS 2005, then you need to perform the restore using T-SQL commands to prevent the long running RESTORE HEADERONLY command. Suresh has already pointed out the KB967205 which talks about a fix for this issue in SSMS 2008.
This posting is provided "AS IS" with no warranties, and confers no rights.
My Blog: http://troubleshootingsql.wordpress.com
SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq