Timeout expires during database restore

Posted on

Question :

I’m using this query to restore a database from a C++ program

RAISERROR ('-- Performing full restore...', 0, 1) WITH NOWAIT 
USE MASTER; 
ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
RESTORE DATABASE [{0}] FROM  DISK = N'{1}'  
WITH  FILE = 1, REPLACE, KEEP_REPLICATION, NOUNLOAD, STATS = 5;
ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE; 
RAISERROR ('-- Performing full restore: Done', 0, 1) WITH NOWAIT; 

On my computer it works as intended, but when I try to restore the database on our customer’s computer an error message pops out saying

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

And this shows right after this info shows up

Processed 1768 pages for database ‘[database]’, file ‘[logic_name]’
on file 1. Processed 4 pages for database ‘[database]’, file
‘[logic_name]_log’ on file 1.

I checked the NETWORK SERVICE user and it has access to the folder of the .bak file. I noticed that under the general Security options (not the ones just for the database I want to restore, the outer ones in the tree of the Microsoft SQL Server Management Studio) the NT AUTHORITYNETWORK SERVICE user was missing, so I created it with the same options as the one I have on my computer right now, but this didn’t change anything.

I set the LockTimeOut to 0 (it was -1), but again, nothing changed.

What can cause this timeout expiration? And how can I have different behaviour on different computers? I guess there is some option that I’m missing, but I really don’t know what else to do.
Thanks!

P.S.: this is the query I use to back up the database

BEGIN 
SET NOCOUNT ON; 
BACKUP DATABASE [{0}] TO  DISK = N'{1}' 
WITH NAME = N'program-Full Database Backup', STATS = 10;
END 

Answer :

You have no problem with permissions (otherwise you’d got “access denied” or “file not found” errors), nither it’s a lock timeout. This timeout is set up in your C++ program, if I’m not mistaken, 30 seconds is the default. SQL Server has no timeouts at all, you should change the property in your application, smth like SqlCommand.CommandTimeout

Leave a Reply

Your email address will not be published.