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

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

  • Comments 3

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

 

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.3282&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476>

------------------------------

 

Cause

======

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'

WITH

NOREWIND,

NOUNLOAD,

STATS = 1

GO

 

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

http://support.microsoft.com/default.aspx?scid=kb;EN-US;967205 

 

Satya Madhuri Krovvidi
SE, Microsoft SQL Server

 

Reviewed by
Amit Banerjee, TL, Microsoft SQL Server

Leave a Comment
  • Please add 3 and 7 and type the answer here:
  • Post
  • thanks to share the great work... hope it will work!

  • 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="dotnetguts.blogspot.com/.../a>

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

Page 1 of 1 (3 items)