SQL Server Scheduled Tasks and PowerShell

SQL Server supports Windows PowerShell, which is a powerful scripting shell that lets administrators and developers automate server administration and application deployment. The PowerShell language supports more complex logic than Transact-SQL scripts, giving administrators and developers the ability to build robust administration scripts. Since PowerShell scripts can also be used to administer other Microsoft products, it gives administrators a common scripting language across servers.

In this article from Amit Banerjee shows an error you might get when trying to automate a PowerShell script using scheduled tasks in SQL Server.

This is another gotcha for setting up scheduled tasks which execute PowerShell scripts. I have a SQL Server instance installed on an Azure virtual machine. I am using a D-Series machine which allows me to store my tempdb files on the D: drive which is a SSD drive. However, the D: drive on Azure virtual machines is not a persistent drive. If you have change the drive letters on your Azure VM, then you can use the PowerShell script in my earlier blog post to identify the temporary drive. So, when the Azure virtual machine restarts the D: drive is re-created and all my folder structure is lost. I already have a scheduled task created on my Azure virtual machine which re-creates the folder structure on the D: drive. The blog post in the reference section has more details on how to achieve this.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s