none
Unable to restore databases from tape: Timeout expired. The timeout period elapsed prior to completion of the operation or the

    Question

  • 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)

    ------------------------------
    ADDITIONAL INFORMATION:

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


     

     

    That's all the further I can get... any suggestions?

     

    I'm VERY perplexed by this...

    vendredi 6 juillet 2007 17:46

Toutes les réponses

  • As 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.
    jeudi 12 juillet 2007 19:41
  • 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)?

     

    EDIT:

    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.

     

    lundi 16 juillet 2007 18:33
  • I would just execute the statement RESTORE HEADERONLY FROM  TAPE = N'\\.\Tape0' WITH  NOUNLOAD in a TSQL editor.
    mardi 17 juillet 2007 16:44
  • 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:30
  •  

    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:53
  • We are actually using a SAN.... I guess my only option is to try and speed up the SAN.

     

    vendredi 24 octobre 2008 13:59
  • 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?

     

    vendredi 24 octobre 2008 14:06
  • 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
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;967205
    mercredi 18 mars 2009 18:54
  • 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 )

    GO

    RESTORE DATABASE <Database name>

    FROM TAPE = '\\.\tape0'

    WITH

    NOREWIND,

    NOUNLOAD,

    STATS = 1

    GO

    DBCC TRACEOFF ( 3228 , 3605 , -1 )

    DBCC TRACEOFF ( 3213 , 3605 , -1 )

    DBCC TRACEOFF ( 3028 , 3605 , -1 )

    GO

    • Proposé comme réponse Chris 4ward mercredi 7 juillet 2010 14:50
    mardi 25 mai 2010 12:47
  • The following blog post talks about this issue:

    http://blogs.msdn.com/b/sqlserverfaq/archive/2009/04/15/not-able-to-restore-backup-from-tape-for-large-databases-on-the-sql-server-2005.aspx

    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
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    mercredi 26 mai 2010 21:45
  • Try moving the Data (*.bak) to local Disk and then try to restore...It works..Had the exact same error
    jeudi 10 mai 2012 03:29