Results 1 to 3 of 3

Thread: How to be certain that a SQL backup ran successfully

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2015

    How to be certain that a SQL backup ran successfully

    I am maintaining a legacy VB6 project. The entire data layer of this project is in a single DLL that connects to a SQL Server using ADO. I can send a query to the data layer dll to perform a database backup, and the backup runs just fine. But what I don't know how to do is ensure that the backup ran successfully. Sending a query to run the backup does not return a recordset of course. When you run the query in Management Console it gives several messages as the backup is running and provides messages that the backup was successful among other things. I cannot find any way to access those messages with VB code.

    Here's what I have tried:
    1) Declaring the ADO connection variable WithEvents in hopes that the InfoMessage event on the connection would provide what I need. Turns out the event only fires for errors, so in my case it doesn't fire at all if the backup is successful.

    2) Created a com exposed .Net component that uses SMO to perform backups. This works in most cases, but later versions of SMO don't play well with VB6 (imagine that), so I really need a solution that works with a sql query to give end users that option on the fly if SMO starts misbehaving.

    If I can't get to the messages in VB6, would it be safe to assume that if the query does not return a recordset and the errors collection of the ADO connection is empty then that means the backup was successful? Surely it can't be that easy.

    Thank you for your time.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002

    Re: How to be certain that a SQL backup ran successfully

    Why is the app handling it? Why not set up a maintenance plan in SQL Server with the SQL Agent that creates backups on a regular basis? You can even have it email you if it succeeds, or if it fails, or both! SQL Server has this stuff built in, let it do all the work.

    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005

    Re: How to be certain that a SQL backup ran successfully

    I was going to post some code but I keep getting page errors
    Sometimes the Programmer
    Sometimes the DBA


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width