Not able to restore backup from tape for large databases on the SQL Server 2005

We get the below error message when we try to restore from tape for large databases.




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)

For help, click: <>





When we use the SQL Server Management Studio GUI to perform a restore of a large database, we encounter the following message:


Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)



These are the steps that take place in the background when the restore is performed in GUI,


 Step 1: Enumerate the backup devices.

 Step 2: Fetch details about the backup that we are trying to restore

 Step 3: Fetch the backup header (This can take time depending on the size of the backup and backup media/device)


RESTORE HEADERONLY FROM  Tape =  ' \\.\tape0' WITH  NOUNLOAD --- this tries to fetch the header information from the tape for a specified amount of time.


 Step 4: Obtain the list of files present in the backup set/media.


 RESTORE FILELISTONLY FROM Tape =  ' \\.\tape0'  WITH  NOUNLOAD,  FILE =(file_number)


 Step 5: Perform the actual restore operation.


 RESTORE DATABASE [Database name] FROM  DISK = Tape =  ' \\.\tape0'  WITH  FILE =(file_number),   NORECOVERY,  NOUNLOAD,  STATS = (percentage)


In GUI this fails for large databases due to the fact that certain operations like the below sequence have a built-in timeout of 20 seconds.

Right click on a database >> Tasks >> Restore >> Database >> From Device >> Click on button >> Backup Media = TAPE >> Add >> Select Backup tape >> OK >> OK.


The timeout will happen on the "Specify Backup" dialog.

This timeout is to prevent these dialog boxes from hanging forever when there is no tape present in the drive.


If you run the "RESTORE HEADERONLY" command from Tsql, you can see that it takes several minutes to complete which is much longer than the 20 second timeout for GUI.



To work around the issue, we can need to use T-SQL from Management Studio.


For the restore:

RESTORE DATABASE <Database name>

FROM TAPE = '\\.\tape0'







UPDATE: 01 June 2010


Please refer below KB as its fixed for SSMS.

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;EN-US;967205 


Satya Madhuri Krovvidi
SE, Microsoft SQL Server


Reviewed by
Amit Banerjee, TL, Microsoft SQL Server

Comments (4)

  1. LTO 4 Tape says:

    thanks to share the great work… hope it will work!

  2. dotnetguts says:

    I was having some what similar issue and I solved it.  I was trying to restore with SQL Server 2008 DB.

    I have documented each step, which help me to resolve this problem.

    Please read this blog: <a href="…/a>

  3. Hey , we tried to check your blog but the path is inaccessible . We would love to post your blog steps as well in here! Looking forward to hearing from you ! 🙂

  4. Dinesh Vishe says:

    Hi ,

    2 percent processed.

    4 percent processed.

    6 percent processed.

    8 percent processed.

    Msg 3634, Level 16, State 1, Line 1

    The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'OpenFile' on 'E:Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATATest_ONLINE.filestreamdata16af32a0-74f5-4513-a8b2-27301072939dc2ed4313-2da4-420d-8dcf-a8c5b02da3240008ecd-000093da-0052'.

    Msg 3013, Level 16, State 1, Line 1

    Data is one tb and mssql 2008r2.

    Suggest me solution..

Skip to main content