Backup aborts halfway through with no error

Sorry to be a little sensational here, but I thought people might like to
know that on a slow PC and big DB that takes more than 30 secs to backup, if
a VB app using ADO to kick off a backup times out it will terminate the
backup process. The only error the user gets is that a timeout has
occurred, and when they check the timestamp of the .BAK backup file it is up
to date, so all looks OK.
Only when they try to restore from the file do they realise it is INCOMPLETE
and that the backup process must have given up half way through without
removing the .BAK file. This could have put serious egg on my face, however
I discovered it during a transfer and no data has been lost.
May I suggest that in the next version - SQL2005 - if a Backup WITH INIT
gets terminated for some reason, that the .BAK file gets deleted? This way
users will know the backup has failed without having to do a test restore.
We all know they SHOULD do that, but this is the real world...
Clive Elsworth, London (UK)
www.EndorphinSoftware.co.uk
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.733 / Virus Database: 487 - Release Date: 03/08/2004
Your application needs to query the msdb database for to see if the backup
was successful. msdb stores all the history of backups and restores. Also
set the ConnectionTimeout property of the ADO object to 0 and the backups
will not timeout anymore.
Jim
"Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in
message news:uEArOGteEHA.248@.TK2MSFTNGP12.phx.gbl...
> Sorry to be a little sensational here, but I thought people might like to
> know that on a slow PC and big DB that takes more than 30 secs to backup,
if
> a VB app using ADO to kick off a backup times out it will terminate the
> backup process. The only error the user gets is that a timeout has
> occurred, and when they check the timestamp of the .BAK backup file it is
up
> to date, so all looks OK.
> Only when they try to restore from the file do they realise it is
INCOMPLETE
> and that the backup process must have given up half way through without
> removing the .BAK file. This could have put serious egg on my face,
however
> I discovered it during a transfer and no data has been lost.
> May I suggest that in the next version - SQL2005 - if a Backup WITH INIT
> gets terminated for some reason, that the .BAK file gets deleted? This
way
> users will know the backup has failed without having to do a test restore.
> We all know they SHOULD do that, but this is the real world...
>
> Clive Elsworth, London (UK)
> --
> www.EndorphinSoftware.co.uk
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.733 / Virus Database: 487 - Release Date: 03/08/2004
>
|||In their case I created a batch file to do the backup using osql, but thank
you for your post.
I use the Data Environment and had tried changing ConnectTimeout and
CommandTimeout to 120, but the backup still aborted after 30 secs.
I now realise I should probably have changed the CommandTimeout of the
command object that does the backup from its default of 30.
Many thanks again
Clive
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:ezId8x1eEHA.2804@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Your application needs to query the msdb database for to see if the backup
> was successful. msdb stores all the history of backups and restores. Also
> set the ConnectionTimeout property of the ADO object to 0 and the backups
> will not timeout anymore.
> Jim
> "Clive Elsworth" <clive@.takethisbitout.elsworth.dircon.co.uk> wrote in
> message news:uEArOGteEHA.248@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
backup,[vbcol=seagreen]
> if
is[vbcol=seagreen]
> up
> INCOMPLETE
> however
> way
restore.
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.733 / Virus Database: 487 - Release Date: 03/08/2004