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?

I don't think its a timeout issue, based on the error msg... — Ezi, May 18 '11 at 18:43
Ii is in the question... "The backup or restore was aborted." — Ezi, May 18 '11 at 19:21

score 6 · Accepted Answer · answered May 18 '11 at 18:40

6

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

answered May 18 '11 at 18:40

Hans Olsson

54,199
15
94
116

@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. – Hans Olsson May 18 '11 at 18:45
your right... the timeout was zero but not the StatmentTimeout. that did the trick. – Ezi May 18 '11 at 19:30

score 2 · Answer 2 · answered May 18 '11 at 18:42

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

answered May 18 '11 at 18:42

Tom Pickles

890
10
26

Backup fails using smo on big databases

2 Answers2