Script to test for file and then write into log

I'm in a little bind.

I have a MS SQL database that does nightly backups and creates a file every night that needs to be backed up.

I need to find a way to test that a backup file was created within the  last 24 hours and if it hasn't, to write to the Windows Logs so that an agent can alert me that there's been an issue.

I do not want to only rely on MS Sql Studio to let me know of failure but I want a Windows way to record this for redundancy.

All the monitoring software that I've found will keep track of changes but I don't know of any that will test a directory for a file and only trigger when the file is not there.

I'm thinking powershell but I don't know the language or even if it's able to do it.

Any suggestions (or a sample script) will be greatly appreciated.
onepiolinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
How do you want to find the file?  Is it always the same name, and you just want to check the created time?  Or does the name change with each backup, etc?

I assume you mean place an entry into a windows event log, and if so which one?

~bp
0
onepiolinAuthor Commented:
Ideally, I'd like to load a batch or powershell file in the Task Scheduler.

The way the SQL Studio works right now is that a file with a random name is created nightly in a specific folder. I just want to check for the creation / modified timestamp. If there isn't a file there that is less than 24 hours old (within a day), I'd like to know about it.

Any of the logs would work but my preference would be the Application Log. I'm using Labtech monitoring software and it can flag when a specific event occurs, so I can check for that event.
0
Bill PrewCommented:
Here is a basic idea in VBS, adjust the folder path as needed and test.  VBS doesn't have any easy way to set the Event Id, so if that is a problem I thing we have to call out to DOS for the command line event create tool to set that.  In which case perhaps we go BAT for everything.  The problem there is doing time 'math' in DOS is rather difficult.  See if this could work.

' Event log constants
Const EVENT_SUCCESS = 0
Const EVENT_ERROR = 1
Const EVEN_WARNING = 2
Const EVENT_INFORMATION = 4
Const EVENT_SUCCESSAUDIT = 8
Const EVENT_FAILUREAUDIT = 16

' Where to search for new backup files, and age to filter by
Const strBaseDir = "b:\ee\ee28689539\files"
Const intAgeInHours = 24

' Creat file system object, access the backup folder
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objBaseDir = objFSO.GetFolder(strBaseDir)

' See if any file is newer than filter criteria
blnFoundNewFile = False
For Each objFile in objBaseDir.Files
   If DateDiff("h", objFile.DateCreated, Now()) < intAgeInHours Then
      blnFoundNewFile = True
      Exit For
   End If
Next

' If no recent backup found write warning to application log
If blnFoundNewFile = False Then
   Set objShell = Wscript.CreateObject("Wscript.Shell")
   objShell.LogEvent EVENT_WARNING, "No SQL backup found in past " & intAgeInHours & " hours in directory """ & strBaseDir & """."
End If

Open in new window

~bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

onepiolinAuthor Commented:
Thank you so much.

I'll test and report.
0
onepiolinAuthor Commented:
This is awesome. It works on my workstation, now I need to tweak for weekends and run in on the server.
0
onepiolinAuthor Commented:
I appreciate the time. Thanks you!
0
Bill PrewCommented:
Welcome.

~bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.