Unable to restore databases from tape: Timeout expired. The timeout period elapsed prior to completion of the operation or the
vendredi 6 juillet 2007 17:46
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
jeudi 12 juillet 2007 19:41As a workaround you could run profile to trace what is happening in the background by the interface when you are getting the error. Copy that code into a query editor and try to run it. If it runs successfully, then you know it is an interface error.
lundi 16 juillet 2007 18:33
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.
mardi 17 juillet 2007 16:44I would just execute the statement RESTORE HEADERONLY FROM TAPE = N'\\.\Tape0' WITH NOUNLOAD in a TSQL editor.
vendredi 24 octobre 2008 13:30
Is there any update on this?
I am experiencing the same problem on my 2008 instance. If the "restore headeronly" command takes too long, the process bombs with a timeout message....
vendredi 24 octobre 2008 13:53
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.
vendredi 24 octobre 2008 13:59We are actually using a SAN.... I guess my only option is to try and speed up the SAN.
vendredi 24 octobre 2008 14:06
Your SAN is timing out...???
That is a bit odd isn't it?
How slow is it? I assume your backups work just fine if you backup to the local drive?
That there isn't some other issue entirely you are experiencing?
mercredi 18 mars 2009 18:54Can 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
mardi 25 mai 2010 12:47
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
mercredi 26 mai 2010 21:45
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
jeudi 10 mai 2012 03:29Try moving the Data (*.bak) to local Disk and then try to restore...It works..Had the exact same error
- Modifié Amatorone jeudi 10 mai 2012 03:30