I had a tip published over at MSSQLTips yesterday and wanted to drop a quick post to brag tell people about it. The tip is very straightforward, it uses xp_fileexist to verify if your database dumps are still on disk or not.
Keep in mind that xp_fileexist is one of those undocumented stored procedures, which is my way of telling you that if you decide to build something using my script it could suddenly stop working one day. Of course, all my scripts are like that but since this one is using an undocumented stored procedure I have the luxury of a scapegoat. Which is nice. Not “total-consciousness-on-my-deathbed” nice, but nice nonetheless.
The whole idea came about because there are a handful of times when files may get moved or deleted accidentally. With this script you can quickly verify a possible issue before you go looking for the file because you need to do a restore. Given the choice between needing to recover a file from tape because you might need to do a restore versus needing it NOW because you MUST do a restore, I will take the former. This script can help, you could put it into a SQL Agent job, or perhaps a local policy, or even Operations Manager. Your choice, really.
I am actually thinking I will take this script and build it into a monitor inside of Operations Manager, giving me the ability to quickly see if there is an issue at periodic times throughout the day.
Tom,
While your T-SQL based solution is awesome, I think that this is a job better suited for Powershell.
sure enough, and i won’t disagree. might i suggest you write that tip and submit it for publication at MSSQLTips?
i thought about using this scenario as an excuse to dive into Powershell, but the fact remains i cannot deploy any Powershell solution to OpsMgr, i am stuck with T-SQL and VBScript when building my customizations. so, i end up continuing to use those technologies.