Ideas on Comparing SQL Records vs. Calendar

[mbutler755]

I have a program that backs-up remote locations to a centralized FTP server. Upon successful completion, a record is written to a SQL database with the following information:

GUID (uniqueidentifier)
StoreID (varchar(5))
DateStamp (sqldatetime)
ProgramAndVersion(varchar(50))

I am currently managing the program by running a report on who has written a record to the database. If the record is not written, I manually go in and backup the necessary files.

The problem with this, is that it can take up to 30 minutes/day to do this task. While that's not a long time, it's getting somebody to be dedicated when I'm off and that has proven to be difficult.

I want the program to automatically search for missing records, but I'm not sure how to do this. I was thinking about bringing in the last 30 days with a SELECT statement and then comparing the information that I have in the SQL database versus a calendar control (or something). I need to make this process completely automated and not very intensive on the SQL server. I need some ideas...

If you have any suggestions, I would love to hear them. Thus far, I only have the following code:

Sub FindMissingBackups(ByVal DaysToCheck As Integer, ByVal StoreNumber As String)
        Dim TodaysDate As SqlDateTime = Now()
        Dim PastDate As SqlDateTime = DateAdd(DateInterval.Day, -DaysToCheck, Now())
 
        MsgBox("Today: " & TodaysDate.ToString & vbNewLine & "Past: " & PastDate.ToString)
        Try
            Dim ConnString As String = "Server=" & _sqlServerAddress & ";Database=" & _sqlDatabase & ";User ID=" & _sqlUsername & ";Password=" & _sqlPassword & ";"
            Dim SqlString As String = "SELECT DATESTAMP FROM " & _sqlTablename & " WHERE STOREID='" & StoreNumber & "' AND DATESTAMP >='" & PastDate.ToString & "'"
            Dim SqlConn As New SqlConnection
            Dim SqlCmd As New SqlCommand
            SqlConn.ConnectionString = ConnString
            SqlConn.Open()
            SqlCmd.Connection = SqlConn
            SqlCmd.CommandText = SqlString
            SqlCmd.ExecuteNonQuery()
            SqlConn.Close()
        Catch ex As Exception
            'Catch any errors here
        End Try
    End Sub