Automatically Create Scripts for All SQL Server Jobs

Automatically create scripts for all SQL Server Jobs

After some further investigation I found a good script that did the job
Generate-Scripts-for-SQL-Server-Objects

The code below is what I needed:

DECLARE @object int
exec sp_OACreate 'SQLDMO.SQLServer', @object OUT
exec sp_OASetProperty @object, 'LoginSecure', TRUE
exec sp_OAMethod @object,'Connect(sqltest)'
exec sp_OAMethod @object, 'Jobserver.Jobs().Script()'
exec sp_OADestroy @object

Thanks for the help anyway

PowerShell code to script out all SQL Server Agent jobs into a single file

Give $FileName a single file name for the whole set. Then you can leave out the whole foreach block:

$FileName = "C:\SQLBackup\SQLJobs\whatever.sql"

$jobs | %{ $_.Script() } | Out-File -filepath $FileName

Create SQL Server job automatically

You would need to dynamically create the job script and then execute it.
You could try something like the following or change this to a stored proc with input parameters for the job owner and database name.

DECLARE @JobName VARCHAR(20)  --Job Name
DECLARE @Owner VARCHAR(200)   --Job Owner
DECLARE @DBName VARCHAR(200)  --Database Name
DECLARE @JobCode VARCHAR(4000) --Create Statement for Job
SET @JobName = 'Test2'
SET @Owner = 'BrianD'
SET @DBName = 'master'
SET @JobCode = 'USE msdb
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
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''' + @JobName + ''', 
        @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''' + @Owner + ''', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''Version and Prod Level'', 
        @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''TSQL'', 
        @command=N''select SERVERPROPERTY(''''productversion''''), SERVERPROPERTY(''''productlevel'''')'', 
        @database_name=N''' + @DBName + ''', 
        @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'
Exec (@JobCode)

Hopefully this will get you going in the right direction. If you need more help let me know.

How can I automate the generate scripts task in SQL Server Management Studio 2008?

What Brann is mentioning from the Visual Studio 2008 SP1 Team Suite is version 1.4 of the Database Publishing Wizard. It's installed with sql server 2008 (maybe only professional?) to \Program Files\Microsoft SQL Server\90\Tools\Publishing\1.4. The VS call from server explorer is simply calling this. You can achieve the same functionality via the command line like:

sqlpubwiz help script

I don't know if v1.4 has the same troubles that v1.1 did (users are converted to roles, constraints are not created in the right order), but it is not a solution for me because it doesn't script objects to different files like the Tasks->Generate Scripts option in SSMS does. I'm currently using a modified version of Scriptio (uses the MS SMO API) to act as an improved replacement for the database publishing wizard (sqlpubwiz.exe). It's not currently scriptable from the command line, I might add that contribution in the future.

Scriptio was originally posted on Bill Graziano's blog, but has subsequently been released to CodePlex by Bill and updated by others. Read the discussion to see how to compile for use with SQL Server 2008.

http://scriptio.codeplex.com/

EDIT: I've since started using RedGate's SQL Compare product to do this. It's a very nice replacement for all that sql publishing wizard should have been. You choose a database, backup, or snapshot as the source, and a folder as the output location and it dumps everything nicely into a folder structure. It happens to be the same format that their other product, SQL Source Control, uses.

SQL Server - running daily jobs with r scipts

Sql-server supports running R and python since sql-server 2014. Take a look on this.

Method before sql-server 2014

However if you are using version before 2014, you need to have some other way else. My suggestion is using R to kick start the sql (if you can).

Using library like taskscheduleR. You first make a schedule task of R for computing your data. And then connect to your sql-server by some library like obdc, dbi, etc. to update what you want.

ps. I didn't try this before, but I can make some test if you still have further problem about it.

How do you script out SQL Server agent jobs to single or individual files

Possibly you're not instantiating the Server object correctly. Try the following instead...

# Alternative 1: With servername and port, using Trusted Connection...
$ServerName = 'YourServerName,1433'
$ServerConnection = New-Object  Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList @( $ServerName )

# Alternative 2: With an SqlConnection object
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$ServerName;Initial Catalog=CED_NCT_RESOURCE_TRACK;Persist Security Info=True;User ID=CEDNCTAdmin;Password=CEDNCTAdmin;"
$SqlConnection.Open() | Out-Null
$ServerConnection = New-Object  Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList @( $SqlConnection )

# Then...
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList @( $ServerConnection )
$Server.JobServer.Jobs | ForEach-Object {
    Write-Host "Job: $($_.Name)"
}

How to start SQL Server agent jobs automatically

When you start SQL Server Agent all jobs (if they are not disabled) will run automatically and it's no need to control each job. Just make sure that jobs are enabled.

SQL Server - how to Generate and Publish Scripts automatically

From this answer, there appear to be tools called SMOScript and ScriptDB that can do that.

If you find a way without third party tools please share :)

weekly Automatic running sql server script

You were on the right track, in management studio:

(1) Find SQL Agent and right click and select Job.

(2) Give you job a name and description and then choose the Steps option on the left

(3) In the Step options you can name your step, insert your script code, AND select which database you want the job to be performed on.

(4) Next go to the Schedules option on the left and designate how often you want the job to run (pretty self-explanatory)

(5) The Alerts, Notifications, etc options should also be utilized if you want to be informed on failure/success/etc.

That's it pretty much.

---

Related Topics