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?

Greenstein

Is there a timeout property for the backup method? – Armure

I don't think its a timeout issue, based on the error msg... – Greenstein

How about sharing the error message with us? – Armure

Ii is in the question... "The backup or restore was aborted." – Greenstein

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

Photina

@Ezi: And you're only creating the one connection? I mean so that there's no risk that you're setting the timeout to 0 on the wrong one? If so I'm afraid that I don't have any other suggestion. – Photina

your right... the timeout was zero but not the StatmentTimeout. that did the trick. – Greenstein

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

Armure