I have an SQL Server database used by a live application that gets backed up overnight, and that backup gets "restored" to another server which stays static for 24 hours (for interactive querying without performance impact on the live servers). I'm trying to create a task to check that latter database to see when the new day's data has been made available.
I have tried writing an SQL task that checks one of the fields for the appropriate date, placing a 5min loop on that to keep checking until the data is found. Unfortunately while the database is being "restored" each night, it becomes unavailable - this makes the task fail (return code 77777) and the loop stop with an error result. I tried altering the "On Error" tab for the task, specifying in the "Output/Error variable handling" section that it should ignore errors with the string that gets returned when the database is unavailable. This works, in that an error isn't raised, but it does still stop the loop, so that isn't going to meet the requirements.
The brief is that I have an SQL database that becomes unavailable for a while each night, and I want to have a task/job in VisualCron that waits for that database to become available and display the correct date in a field. Does anyone do anything similar? Any thoughts on how I might get this to work?
Any help appreciated!!