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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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 PrewIT / Software Engineering ConsultantCommented:
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?

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.
Bill PrewIT / Software Engineering ConsultantCommented:
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

' 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

' 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


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
Defend Against the Q2 Top Security Threats

Were you aware that overall malware worldwide was down a surprising 42% from Q1'18? Every quarter, the WatchGuard Threat Lab releases an Internet Security Report that analyzes the top threat trends impacting companies worldwide. Learn more by viewing our on-demand webinar today!

onepiolinAuthor Commented:
Thank you so much.

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

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

From novice to tech pro — start learning today.