Backup fails using smo on big databases

Question

I use vb.net to backup sql '05 - '08 databases. It works great on smaller databases. but when it comes to large databases it fails at 30 or 40 percent with the error: The backup or restore was aborted.

        Dim objBackup1 As Backup = New Backup() With {.Action = BackupActionType.Database, .Database = Common.DsSettings("DataBase", Nothing), .Initialize = True, .Checksum = True, .ContinueAfterError = True, .Incremental = False, .LogTruncation = BackupTruncateLogType.Truncate}

    objBackup1.SqlBackup(objServer)

Any idea on how to overcome this problem?

Answer 1

Can't remember exactly, but I think it might be that the operation is timing out. I think the default timeout is 10 minutes, but if you set it to 0 it'll disable the timeout.

Something like:

conn.StatementTimeout = 0

Answer 2

Have a look at this, looks like a timeout issue:

http://devio.wordpress.com/2009/03/27/handling-smo-sqlbackup-timeout/

and

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnection.statementtimeout.aspx?ppud=4