Sometimes we need to check for some requirements before running a job. For instance, let’s say that we want to check that a file exists before running a job that tries to restore a database. Before running the job we want to make sure that a file named “testdb.bak” exists, otherwise we will have an error running the t-sql command. In order to check whether the file exists or not we have several options but in this post we will focus on Powershell solutions, in fact we have two ways to face it:

  • Using the powershell Step type in SQL Server Agent
  • Using the Operating System Step type in SQL Server Agent

Using Powershell Step

What we need to do first is to write the powershell code to check wether the file exists or not. We will use the Test-Path cmdlet. This cmdlet allows us to test whether a path exists returning true or false.

the main powershell code looks as follows:

Test-Path -Path "C:temptestdb.bak"

When we run this on powershell we got the following result:

So what we need to do next is to create an script to generate an error if the file does not exists. The script code is:

if ( (Test-Path -Path "C:temptestdb.bak") -eq $false)
{
	Write-error -Message "Error: testdb.bak does not exist."
}

If we run the script let’s see what happens:

In case that does not exists we will got the following result:

As we can see we are getting the error with the message that we just wrote: “Error: testdb.bak does not exist”

In case that exists we won’t get any error:

Now that we have the code ready we will go and write the job step. So we add a new step and we specify the type as Powershell and write the code.

Apparently everything should work fine. We got our powershell code to tell us whether the file exists or not as the first step in the job and the second step will be the t-sql code to restore the database. When we run it we get this result:

The job fails. The reason why the job fails is because the file to restore does not exist, because I renamed it from testdb.bak to testdb222.bak as we can see in the following picture:

If we check the job history we see that the fail was generated running the step 2 (restore statement) because the file testdb.bak doesn’t exist. This doesn’t make sense because we created the powershell step just check that condition and try to avoid this situation:

The reason why this job is not failing in the step 1 is because when we run powershell scripts we need to send to SSMS a signal to let Management Studio know that some errors happened. In order to solve this situation we need to send that signal throwing an exception from powershell like follows:

cls
if ( (Test-Path -Path "C:temptestdb.bak") -eq $false)
{
	$err="Error: testdb.bak does not exist."
	throw $err
}

Now when we run the job again we will get also an error but this time the error will be generated running the step 1, which means that our check works fine Smile.

Using Operating System Step

If we don’t have SQL Server 2008 installed we won’t be able to create powershell steps on SQL Server jobs. But this doesn’t mean that we can’t use powershell. We can use the operating System step and run the same powershell script that we run before just executing powershell as an executable. The operating system step does the same that the windows command prompt, so in order to run the script we can do it as follows:

Note: If you don’t have SQL Server 2008 or later versions and windows server 2008 or later versions you will have to install Powershell in your system.

I hope this post will be useful for you!!

Happy scripting :)!