SMO.Restore.SqlRestore sometimes throws a timeout exception on deployed computers

Question

I have a app used for managing databases for demos of our software, one of the things that it does is get a copy of a database from a central server and restore it to a local SQL instance. Everything works correctly on the backup portion but on the restore some people are reporting that they get the following exception in the middle of the restore.

Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 'Computername'.
---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: 
     An exception occurred while executing a Transact-SQL statement or batch. 
     ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
          at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
          (snip)
          at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
      --- End of inner exception stack trace ---
      at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
      (snip)
      at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at ContractFlowTool.WebInfinity2.AttachDatabase.RestoreLocal(AttachDatabaseArgs arg)

The MSDN is fairly light on the inner working of the SMO classes. I could not find any method to change the timeout time for executing the restore. What can I do so the exception will not happen?

---

Here is the code performing the restore

private static bool RestoreLocal(AttachDatabaseArgs arg)
{
    if (arg.DestDatabase == null)
        throw new ArgumentNullException("DestDatabase");
    SqlConnectionInfo serverConnInfo = new SqlConnectionInfo(/*snip*/);
    ServerConnection serverConn = null;
    serverConn = new ServerConnection(serverConnInfo);
    var remoteServer = new Server(serverConn);
    var clinicFolder = ClinicFolder(arg);
    var restore = new Restore();
    restore.PercentCompleteNotification = 5;
    restore.NoRecovery = false;
    restore.RelocateFiles.Add(/*snip mdf*/);
    restore.RelocateFiles.Add(/*snip ldf*/);
    restore.Database = arg.LocalDB;
    restore.ReplaceDatabase = true;
    restore.Action = RestoreActionType.Database;
    restore.PercentComplete += arg.ProgressForm.Restore_PercentComplete;

    restore.SqlRestore(remoteServer);
}

Vociferous

Can you reproduce this personally? Is database size a factor? Are there any really huge binary fields that might be causing this? Does the timeout occur after a consistent time span? If you're really stuck, you might have a little luck digging into the actual assemblies using Reflector to see how they work (and replicating it, probably through reflection (to get at private/internal members), with a higher timeout). Have you seen this? – Erewhile

Answer 1

Thanks to Camerons suggestion the solution to this issue was that I need to set ServerConnection.StatementTimeout higher. Apparently this happens frequently when databases are larger than 3GB.

(...) 
serverConn = new ServerConnection(serverConnInfo);
serverConn.StatementTimeout = 240; //<- set this.
var remoteServer = new Server(serverConn);
var clinicFolder = ClinicFolder(arg);
(...)

Vociferous

Answer 2

U could use this ,specially when you can't modify connection string or modification of timeout in connection string does not work It works for me for a mirrored database over 2 GB

Using (SqlConnection1)

Dim sqlStmt As String = String.Format("BACKUP DATABASE map TO DISK='{0}'", backup_directory + backupfile)

            Using bu2 As New SqlCommand(sqlStmt, SqlConnection1)
                SqlConnection1.Open()
                bu2.CommandTimeout = 180   //this line is the key
                bu2.ExecuteNonQuery()
                SqlConnection1.Close()
            End Using
        End Using

Penthouse

Yes, StatementTimeout maps to CommandTimeout, however this question was about using SMO not SqlCommand – Vociferous