Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Archive for the ‘Powershell’ Category

Script out SQL agent jobs from powershell

Posted by rahmanagoro on August 26, 2010


At my shop, we currently use SVN to store database objects, one object type which is often overlooked is SQL agent jobs, I needed a way in which I can generate sql agent jobs into sql files, SQL management studio allows you to script jobs, but you can either do this one at a time, or script all the jobs in one big file. The two options are not really ideal for me, the reason being that I may choose to be selective about which jobs I install on a server, other jobs are installed as part of an installation suite, e.g management data warehouse in SQL 2008.

I have written a simple powershell script that will simple script out all SQL agent jobs, one might have multiple servers, I have made the script such that it reads a text file for a list of servers, simply save this file on a location and call the powershell script, you also need to specify where you want the script to be generated to as well.


#Original
# Load SMO extension

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# Get List of sql servers to check

#$sqlservers = Get-Content "$Env:USERPROFILE\sqlservers.txt";
$sqlservers = Get-Content "C:\Servers.txt";

# Loop through each sql server from sqlservers.txt
foreach($sqlserver in $sqlservers)

{

      # Create an SMO Server object
      $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;

      # Jobs counts
      $totalJobCount = $srv.JobServer.Jobs.Count;
      $failedCount = 0;
      $successCount = 0;

      # For each jobs on the server
      foreach($job in $srv.JobServer.Jobs)

      {
            # Default write colour
            $colour = "Green";
            $jobName = $job.Name;
            $jobEnabled = $job.IsEnabled;
            $jobLastRunOutcome = $job.LastRunOutcome;
 			$jobNameFile = "C:\SQLJOBS\" + $jobName+".sql"

 			Write-Host $job.Name
			Write-Host "The location of the file is called " $jobNameFile

#			[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
#			$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
#			#$srv.JobServer.Jobs | foreach {$_.Script()} | out-file -path $path
			$job | foreach {$_.Script()} | out-file $jobNameFile

            # Set write text to red for Failed jobs
            if($jobLastRunOutcome -eq "Failed")

            {

                  $colour = "Red";
                  $failedCount += 1;
            }

            elseif ($jobLastRunOutcome -eq "Succeeded")
            {
                  $successCount += 1;
            }

            Write-Host -ForegroundColor $colour "SERVER = $sqlserver JOB = $jobName ENABLED = $jobEnabled LASTRUN = $jobLastRunOutcome";
      }

      # Writes a summary for each SQL server
      Write-Host -ForegroundColor red "=========================================================================================";
      Write-Host -ForegroundColor red "$sqlserver total jobs = $totalJobCOunt, success count $successCount, failed jobs = $failedCount.";
      Write-Host -ForegroundColor red "=========================================================================================";
}
Advertisements

Posted in Day to day queries, Powershell, Professional Development, Scripts, SQL 2008, Tips | 4 Comments »

Powershell secret timeout ! Running SQL from powershell V1

Posted by rahmanagoro on August 26, 2010


I have always been very keen to learn new aspects of SQL server and as much as possible use new technology. When SQL 2008 came out about 2 years ago, powershell technology has always been one that’s interests me, I like the fact that almost any task can now be scripted. I liked the fact that, I can write a script and push it out to as many SQL instances as possible. I also like the fact that tasks in windows can also be scripted as well, so lets say I want to collect perfmon data and integrate this with SQL server, it is now easy to achieve this.

To this note, I was inclined to use powershell towards running backup stored procedures, although I could have easily used TSQL to do this, but as I know, powershell is the future and as a DBA, I don’t want to be left behind. I also liked the fact that, within my backup scripts I want to delete older backups after they have been backed up to tape, powershell allows me to easily do this without having to write lines of scripts.

I found a sample scripts on:

To my surprise, I started to run my powershell scripts which backs up SQL databases via stored procedures, all of a sudden they started failing. The stored procedure was fairly straight forward and simple, but yet the scripts will still fail. What I noticed was that for differential backups, the powershell script runs fine, but for full backups the powershell scripts which calls the stored procedure fails. This occurred to me like some kind of issue with timeout, at first it sounded strange as I didn’t specify a timeout period, but yet the query was failing.

Even a simple query like the one below will fail as well

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "  waitfor delay '00:00:50'"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]


I later confirmed from Microsoft MVP Grant Fitchley that there is a 30 second default timeout, this is something that everybody should be aware of in order to avoid this problem. I also note that a connect ticket has been opened for this bug, see https://connect.microsoft.com/PowerShell/feedback/details/523996/invoke-sqlcmd-querytimeout?wa=wsignin1.0

As a workaround, a timeout value was passed into the connection properties.
If a timeout value of 0 is specified, then the script will not time out.

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = " waitfor delay '00:00:35'"
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandTimeout = 0
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

Posted in Powershell, SQL 2008, SQL Administration | Leave a Comment »