Have you ever had a script which runs great against multiple SQL instances but then you find it failing on a new instance?
I have a script which backs up all my SQL Server databases and then copies the local backups to warehouse storage. The script runs as a Windows Scheduled task on our storage server for each SQL Instance.
Usually when I get a new SQL Server, I just add a new Windows Scheduled Task on our storage server, adjust the param to point to the new server, and the script backs up all the databases on the server\instance. Great! However, I recently found a job in which the backups were failing consistently. Not good! . It was a fairly new “test” server so I probably did not pay as close attention to it as I should have when I first set it up.
The bottom line causing the problem The only reason I knew of the failures was because of having I set up the script to write and event log for any error. However,, it was one of those “catch-all” errors with little significant information. If you don’t know already, debugging a Powershell script executed via a scheduled task is no easy task. Error codes are not easy to get. Traces are not easy to get. All in all, this type of debugging is a real PITA.
What made this even more confusing is the script ran perfectly when run from the console. No problems! BTW, I have at least 40 of these tasks running each evening without error. After a few hours of putting in numerous traps and write event logs I finally found the reason it would only fail when run as a Windows Task. The real cause of the problem was the “Log on as” user for the scheduled task did not have access to the SQL Instance! My normal testing used MY Windows credentials which has complete access rights.
Say hello to my little friend, RUNAS!
When starting a Powershell session, the run-time system sets up the environment with your Windows credentials and uses them for access to all resources. In order to have an environment whereby you don’t have to login to Windows as another user, you can use the RUNAS command (in most circumstances). Using RUNAS, I was able to start a PowerGUI session in which my credentials were the same as those being used as the logon for the Windows Task. Now, I was able to thoroughly debug the script using breaks, traces, etc. I now have a desktop Icon which starts up PowerGUI as that user for whenever I need it. You can find out more about RUNAS here, and the command I am using looks similar to this:
C:\Windows\System32\runas.exe /user:Domain\Login "C:\Program Files (x86)\PowerGUI\ScriptEditor.exe"