Database Restoring Failed with Timeout Expired

Question

 I have full  backup file, when I tried to restore Database, Timeout Expired occurs.

And I Tried to resolve this issue with below steps even no luck for me. 

 1. Open SQL Server Management Studio  . Tools---> Options---> Query Execution---> SQL SERVER ----> General: Exection Timeout. I set to '0'

 2.Open SQL Server Management Studio . Query---->Query Options---> Execution--->General---->Execution time-out, set to 0

 3.Even I restarted SQL SERVER . Same error again getting.

 

 

 

Answer 1

Hi,

Can you try to do it using query instead of SSMS e.g:

USE [master]
RESTORE DATABASE [TEST] FROM  DISK = N'D:\Test.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

---

Check out my blog SQLactions.com | This posting is provided “AS IS” with no warranties, and confers no rights.

If this reply answers your question, please mark is as Answered for others to find it easily. If this reply helps you resolving the problem, please vote the post as Helpful.

Answer 2

Run the following command to check "lock timeout", and "remote query timeout".

-------------------------------------------------------
SELECT @@LOCK_TIMEOUT AS [LockTimeOut]
-------------------------------------------------------
SELECT
cfg.name AS [Name],
cfg.value AS [ConfigValue],
cfg.value_in_use AS [RunValue],
cfg.description AS [Description]
FROM sys.configurations AS cfg
WHERE name = 'remote query timeout (s)'
-------------------------------------------------------

If lock timeout <> -1 Then
Tool --> Options --> Query Execution --> Advanced --> SET LOCK TIMEOUT

If Remote Query Timeout is too small Then
Right Click Server --> Properties --> Connections --> Remote Query Timeout

Answer 3

this generally happens when the size of the database backup file is large, as suggested by Prashant try to restore the DB using query window, otherwise you can also validate the backup file by using below command and see if it shows backup details.

RESTORE HEADERONLY 
FROM DISK = N'C:\AdventureWorks-FullBackup.bak' 
WITH NOUNLOAD;
GO

Answer 4

what is the size of the backup file

as sql server 2008 expresss edtion supports only 10 GB of database size and SQL Server 2005 Exresss Edition supports only 4 GB of size

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 5

Hi Ramu,

Please apply the latest SQL Server Service Pack to solve the issue.
In addition, to work around this problem, use Transact-SQL commands to back up and to restore the database instead of using the SQL Server Management Studio user interface.

Refer to: http://support.microsoft.com/kb/967205.

Thanks,
Maggie

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.