//
you're reading...
SQL Server

Using PowerShell in a SQL Server Job

This is a quick post on integrating PowerShell into a SQL Server agent job. I wanted to create an automated process to backup jobs on a weekly basis. I figured probably the easiest approach would be to use SMO and the scripting object. The I set upon writing a quick C# script and then I thought why not PowerShell? I have used PowerShell quite a bit outside of SQL Server that accessed SQL Server objects but not to much integrated into a job. So I created a job,  set the subsystem to PowerShell and imbedded my script as a quoted command. Viola that’s all there is to it. I am working on an expanded version that will script out any database objects to separate script files.

USE [msdb]
GO
 
/****** Object:  Job [Backup SQL Agent Jobs]   ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’DBA – Backup SQL Agent Jobs’,
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N’No description available.’,
        @category_name=N'[Uncategorized (Local)]’,
        @owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Backup SQL Agent Jobs]     ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’DBA – Backup SQL Agent Jobs’,
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N’PowerShell’,
        @command=N'[System.Reflection.Assembly]::LoadWithPartialName(”Microsoft.SqlServer.Smo”) | Out-Null
$serverInstance = "DELL"
 
$server = New-Object (”Microsoft.SqlServer.Management.Smo.Server”) $serverInstance
 
$jobs = $server.JobServer.Jobs
#$jobs = $server.JobServer.Jobs | where-object {$_.category -eq "[your category]"}
 
if ($jobs -ne $null)
{
 
$serverInstance = $serverInstance.Replace("\", "-")
 
ForEach ( $job in $jobs )
{
$FileName = "C:\SQLBackup\SQLJobs\" + $serverInstance + "_" + $job.Name + ".sql"
$job.Script() | Out-File -filepath $FileName
}
}’,

        @database_name=N’master’,
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
 
GO

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: