Script out Jobs & Logins Master DB

Question

Script out Jobs & Logins Master DB

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 19, 2012 at 8:08 am

#251571

How do you script out Jobs, Logins & Linked Servers in SQL Server 2005?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

I'll look for or create a script for linked servers;

the problem is the credentials for the remote user, if you identify one, cannot be scripted out, because they are part of a CREDENTIAL object;

i did see a blog post about doing it via powershell, but i don't know if the password issue occurs there yet, either.

you can do it one by one via the GUI:

For Jobs,

from another post that just happened today, i just tested this and it ran fine:

http://qa.sqlservercentral.com/Forums/Topic1238602-1550-1.aspx

/**************************************Script to generate all the Jobs on a server **************************************/

--CreatedBy - Amit Mathur (v-amat)

--CreatedDate - 08/26/2009

/**************************************Script to generate all the Jobs on a server **************************************/

SET NOCOUNT ON

BEGIN TRY

PRINT 'USE [msdb]'

PRINT 'GO'

PRINT ''

DECLARE @JobID nvarchar(100),

@JobName varchar (128),

@JobCategory varchar (128),

@JobCategoryClass varchar(128),

@Now datetime,

@Nowtext varchar(30)

SELECT @Now = GETDATE()

SELECT @Nowtext = CAST(@Now as varchar(30))

CREATE TABLE #Jobs (id int identity (1,1), jobid varchar(50))

INSERT INTO #Jobs (jobid) SELECT jobid = convert(varchar(50),job_id) FROM msdb.dbo.SysJobs WITH (NOLOCK)

DECLARE @MaxJobs int,

@JobControl int

SELECT @JobControl = 1

SELECT @MaxJobs = MAX(id) FROM #jobs

--Create Jobs by looping through all the existing jobs on the server

WHILE (@JobControl <= @MaxJobs)

BEGIN --BEGIN Jobs

SELECT @JobID = JobID FROM #jobs WHERE id = @JobControl

SELECT @JobName = name FROM msdb.dbo.sysjobs_view WHERE Job_ID = @JobID

SELECT @JobCategory = sc.name, @JobCategoryClass = category_class FROM msdb.dbo.sysjobs sj

INNER JOIN msdb.dbo.syscategories sc

ON sc.category_id = sj.category_id

WHERE Job_ID = @JobID

PRINT '/****** Object: Job ' + @JobName + ' Script Date:' + @Nowtext + ' ******/'

PRINT 'IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N''' + @JobName + ''')'

PRINT 'EXEC msdb.dbo.sp_delete_job @job_name= N''' + @JobName + ''''+ ', @delete_unused_schedule=1'

PRINT 'GO'

PRINT ''

PRINT '/****** Object: Job ' + @JobName + ' Script Date:' + @Nowtext + ' ******/'

PRINT 'BEGIN TRANSACTION'

PRINT 'DECLARE @ReturnCode INT'

PRINT 'SELECT @ReturnCode = 0'

PRINT '/****** Object: JobCategory ' + QUOTENAME(@JobCategory) + ' Script Date:' + @Nowtext + ' ******/'

PRINT 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name = N''' + @JobCategory + ''' AND category_class = ' + @JobCategoryClass+ ')'

PRINT 'BEGIN'

PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name = N''' + @JobCategory + ''''

PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

PRINT ''

PRINT 'END'

PRINT ''

PRINT 'DECLARE @jobId BINARY(16)'

PRINT ''

DECLARE @enabled int,

@notify_level_eventlog int,

@notify_level_email int,

@notify_level_netsend int,

@notify_level_page int,

@delete_level int,

@description nvarchar(128),

@category_name nvarchar(128),

@owner_login_name nvarchar(128),

@notify_email_operator_name nvarchar(128)

SELECT @enabled = sj.enabled,

@notify_level_eventlog = sj.notify_level_eventlog,

@notify_level_email = sj.notify_level_email,

@notify_level_netsend = sj.notify_level_netsend,

@notify_level_page = sj.notify_level_page,

@delete_level = sj.delete_level,

@description = sj.[description],

@category_name = sc.name,

@owner_login_name = SUSER_NAME(sj.owner_sid),

@notify_email_operator_name = so.name

FROM msdb.dbo.sysjobs sj

INNER JOIN msdb.dbo.syscategories sc

ON sc.category_id = sj.category_id

LEFT OUTER JOIN msdb.dbo.sysoperators so

ON sj.notify_email_operator_id = so.id

WHERE Job_ID = @JobID

PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N''' + @JobName + ''','

PRINT ' @enabled=' + CAST(@enabled as varchar(30))+ ','

PRINT ' @notify_level_eventlog=' + CAST(@notify_level_eventlog as varchar(30))+ ','

PRINT ' @notify_level_email=' + CAST(@notify_level_email as varchar(30))+ ','

PRINT ' @notify_level_netsend=' + CAST(@notify_level_netsend as varchar(30))+ ','

PRINT ' @notify_level_page=' + CAST(@notify_level_page as varchar(30))+ ','

PRINT ' @delete_level=' + CAST(@delete_level as varchar(30))+ ','

PRINT ' @description=N''' + REPLACE(@description, '''','''''') + ''','

PRINT ' @category_name=N''' + @category_name + ''','

PRINT ' @owner_login_name=N''' + ISNULL(@owner_login_name,'sa') + ''','

IF @notify_email_operator_name IS NOT NULL

BEGIN

PRINT ' @notify_email_operator_name=N''' + @notify_email_operator_name + ''', @job_id = @JobID OUTPUT'

END

ELSE

BEGIN

PRINT ' @job_id = @JobID OUTPUT'

END

PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

PRINT ''

--CREATE STEPS

DECLARE @MaxSteps int,

@LoopControl int

SELECT @LoopControl = 1

SELECT @MaxSteps = MAX(step_id) FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID

WHILE (@LoopControl <= @MaxSteps)

BEGIN

DECLARE @step_name nvarchar (128),

@step_id int,

@cmdexec_success_code int,

@on_success_action int,

@on_success_step_id int,

@on_fail_action int,

@on_fail_step_id int,

@retry_attempts int,

@retry_interval int,

@os_run_priority int,

@subsystem nvarchar (128),

@command nvarchar (max),

@database_name nvarchar(128),

@flags int

SELECT @step_name = step_name,

@step_id = step_id,

@cmdexec_success_code = cmdexec_success_code,

@on_success_action = on_success_action,

@on_success_step_id = on_success_step_id,

@on_fail_action = on_fail_action,

@on_fail_step_id = on_fail_step_id,

@retry_attempts = retry_attempts,

@retry_interval = retry_interval,

@os_run_priority = os_run_priority,

@subsystem = subsystem,

@command = command,

@database_name = database_name,

@flags = flags

FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID

AND step_id = @LoopControl

PRINT ''

PRINT '/****** Object: Step ' + @step_name + ' Script Date: ' + @Nowtext + '******/'

PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''' + @step_name + ''','

PRINT ' @step_id=' + CAST(@step_id as varchar(30))+ ','

PRINT ' @cmdexec_success_code=' + CAST(@cmdexec_success_code as varchar(30))+ ','

PRINT ' @on_success_action=' + CAST(@on_success_action as varchar(30))+ ','

PRINT ' @on_success_step_id=' + CAST(@on_success_step_id as varchar(30))+ ','

PRINT ' @on_fail_action=' + CAST(@on_fail_action as varchar(30))+ ','

PRINT ' @on_fail_step_id=' + CAST(@on_fail_step_id as varchar(30))+ ','

PRINT ' @retry_attempts=' + CAST(@retry_attempts as varchar(30))+ ','

PRINT ' @retry_interval=' + CAST(@retry_interval as varchar(30))+ ','

PRINT ' @os_run_priority=' + CAST(@os_run_priority as varchar(30))+ ', @subsystem=N''' + @subsystem + ''','

PRINT ' @command=N''' + REPLACE(@command, '''','''''') + ''','

PRINT ' @database_name=N''' + @database_name + ''','

PRINT ' @flags=' + CAST(@flags as varchar(30))

PRINT ''

SELECT @LoopControl = @LoopControl + 1

END -- End Steps While

PRINT ''

PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

PRINT 'EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1'

PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

PRINT ''

--CREATE SCHEDULES

DECLARE @MaxSchedules int,

@SchedulesLoopControl int

SELECT @SchedulesLoopControl = 1

CREATE TABLE #Schedules (id int identity (1,1), schedule_id int)

INSERT INTO #Schedules (schedule_id) SELECT schedule_id = sjs.schedule_id

FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)

--INNER JOIN msdb.dbo.sysschedules ss WITH (NOLOCK) ON sjs.schedule_id = ss.schedule_id

WHERE sjs.Job_ID = @JobID

SELECT @MaxSchedules = MAX(id) FROM #Schedules

IF EXISTS (SELECT COUNT(*) FROM #Schedules)

BEGIN

WHILE (@SchedulesLoopControl <= @MaxSchedules)

BEGIN

DECLARE @name nvarchar(2000),

@sch_enabled int,

@freq_type int,

@freq_interval int,

@freq_subday_type int,

@freq_subday_interval int,

@freq_relative_interval int,

@freq_recurrence_factor int,

@active_start_date int,

@active_end_date int,

@active_start_time int,

@active_end_time int,

@schedule_uid nvarchar (50)

SELECT @name = name,

@sch_enabled = enabled,

@freq_type = freq_type,

@freq_interval = freq_interval,

@freq_subday_type = freq_subday_type,

@freq_subday_interval = freq_subday_interval,

@freq_relative_interval = freq_relative_interval,

@freq_recurrence_factor = freq_recurrence_factor,

@active_start_date = active_start_date,

@active_end_date = active_end_date,

@active_start_time = active_start_time,

@active_end_time = active_end_time,

@schedule_uid = schedule_uid

FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)

INNER JOIN msdb.dbo.sysschedules ss WITH (NOLOCK) ON sjs.schedule_id = ss.schedule_id

INNER JOIN #Schedules s ON ss.schedule_id = s.schedule_id

WHERE sjs.Job_ID = @JobID

AND s.id = @SchedulesLoopControl

PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N''' + REPLACE(@name, '''','''''') + ''','

PRINT ' @enabled=' + CAST(@sch_enabled as varchar(30))+ ','

PRINT ' @freq_type=' + CAST(@freq_type as varchar(30))+ ','

PRINT ' @freq_interval=' + CAST(@freq_interval as varchar(30))+ ','

PRINT ' @freq_subday_type=' + CAST(@freq_subday_type as varchar(30))+ ','

PRINT ' @freq_subday_interval=' + CAST(@freq_subday_interval as varchar(30))+ ','

PRINT ' @freq_relative_interval=' + CAST(@freq_relative_interval as varchar(30))+ ','

PRINT ' @freq_recurrence_factor=' + CAST(@freq_recurrence_factor as varchar(30))+ ','

PRINT ' @active_start_date=' + CAST(@active_start_date as varchar(30))+ ','

PRINT ' @active_end_date=' + CAST(@active_end_date as varchar(30))+ ','

PRINT ' @active_start_time=' + CAST(@active_start_time as varchar (30)) + ','

PRINT ' @active_end_time=' + CAST(@active_end_time as varchar (30)) + ','

PRINT ' @schedule_uid=N''' + @schedule_uid + ''''

PRINT ''

PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

PRINT ''

SELECT @SchedulesLoopControl = @SchedulesLoopControl + 1

END -- End Schedules While loop

END -- END IF (SELECT COUNT(*) FROM #Schedules) > 0

DECLARE @server_name varchar(30)

SELECT @server_name = CASE server_id WHEN 0 THEN 'local' ELSE 'Multi-Server' END

FROM msdb.dbo.sysjobservers WHERE Job_ID = @JobID

PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name =N''(' + @server_name + ')'''

PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

PRINT 'COMMIT TRANSACTION'

PRINT 'GOTO EndSave'

PRINT 'QuitWithRollback:'

PRINT ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'

PRINT 'EndSave:'

PRINT ''

PRINT 'GO'

PRINT ''

SELECT @JobControl = @JobControl + 1

DROP TABLE #Schedules

END --End Jobs

DROP TABLE #Jobs

END TRY

BEGIN CATCH

DROP TABLE #Jobs

DROP TABLE #Schedules

END CATCH;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Lowell,

Nice.:cool:

Thank you!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

savethytrees SSCommitted Points: 1968 More actions · Answer 3

For jobs, I would rather use a VB script. The TSQL method puts a limitation on the length of a job step that is being scripted.

Check this

http://saveadba.blogspot.com/2011/12/sql-server-2005-generate-scripts-for.html

Check out the attached script to generate logins and users.

Blog
http://saveadba.blogspot.com/

bruce 1565 SSCrazy Points: 2574 More actions · Answer 4

Here's a Powershell version. Use the first line if you're running from a Powershell session with SQL 2008 DLLs. The second line if you only have SQL 2005 SMO installed and the third line if all else fails.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop

# Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop

# [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

$server = 'Your_Server_Name_Here'

$srvObj = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $server

## Jobs

$outFile = "${server}_Jobs.sql"

if (Test-Path $outFile ) { Remove-Item $outFile }

$srvObj.JobServer.Jobs | foreach { $_.Script() | Out-File $outFile -Append }

## Linked Servers

$outFile = "${server}_LinkedServers.sql"

if (Test-Path $outFile ) { Remove-Item $outFile }

$srvObj.LinkedServers | foreach { $_.Script() | Out-File $outFile -Append }

## Logins

$outFile = "${server}_Logins.sql"

if (Test-Path $outFile ) { Remove-Item $outFile }

$srvObj.Logins | foreach { $_.Script() | Out-File $outFile -Append }

Powershell has the same issue with the passwords for both logins and linked servers.

Linked server will show

/* For security reasons the linked server remote logins password is changed with ######## */

And Logins have this warning

/* For security reasons the login is created disabled and with a random password. */

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

savethytrees (1/19/2012)
For jobs, I would rather use a VB script. The TSQL method puts a limitation on the length of a job step that is being scripted.
Check this
http://saveadba.blogspot.com/2011/12/sql-server-2005-generate-scripts-for.html
Check out the attached script to generate logins and users.

What do you specify for?

strFilename = "C:\DR_Files\CreateJobs.sql"

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

savethytrees SSCommitted Points: 1968 More actions · Answer 6

That is the location and name of the file that will have all the create job commands. Replace the directory with the directory you want the file to be stored.

You can run this as a VB script job step.

Blog
http://saveadba.blogspot.com/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

savethytrees (1/19/2012)
For jobs, I would rather use a VB script. The TSQL method puts a limitation on the length of a job step that is being scripted.
Check this
http://saveadba.blogspot.com/2011/12/sql-server-2005-generate-scripts-for.html
Check out the attached script to generate logins and users.

Nice script it adds users to the db_denydatareader & db_denydatawriter that are sysadmins.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

savethytrees (1/19/2012)
For jobs, I would rather use a VB script. The TSQL method puts a limitation on the length of a job step that is being scripted.
Check this
http://saveadba.blogspot.com/2011/12/sql-server-2005-generate-scripts-for.html
Check out the attached script to generate logins and users.

Malicious Script!

You should be banned!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

savethytrees SSCommitted Points: 1968 More actions · Answer 9

Please show me the line where it does that. I have no malicious intent so if the script does that then I will remove my script and never use it.

The script will only generate the commands based on the current settings of your logins and users.

If a particular login is currently configured to be denied access or be part of db_denydatareader & db_denydatawriter then it will simply generate the commands.

But under no circumstances it is supposed to generate a DENY command based on an existing login that is part of SYSADMIN but not part of db_denydatareader or db_denydatawriter

Please check the output of the script once you run it and take a look at the settings of the login/user. I am sure the particular user you are referring to is already part of db_denydatareader & db_denydatawriter.

Blog
http://saveadba.blogspot.com/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Where did you get that script?

Try running it on a production box.:w00t:

I had a Developer who was a member of the fixed database role db_owner and it added him to the db_denydatereader and db_denydatawriter.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

savethytrees SSCommitted Points: 1968 More actions · Answer 11

I found the script from here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111431

I have tested the script several times and not once it has generated a DENY command for users that are not part of the db_denydatareader or db_denydatawriter.

It fetches the current status using the following SQL

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin

FROM

sys.server_principals p LEFT JOIN sys.syslogins l

ON ( l.name = p.name )

WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = 'yourlogin'

And then checks and generates that command using the following SQL

IF (@denylogin = 1)

BEGIN -- login is denied access

SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

END

Later it gets the databaseroles using the following SQL

Select name FROM [@DB_Name].[sys].[database_principals]

WHERE [principal_id]

IN (SELECT [role_principal_id] FROM [@DB_Name].[sys].[database_role_members]

WHERE [member_principal_id] = @DB_principal_id)

To find the principal Id run the following SQL

SELECT u.[principal_id], l.[name], u.default_schema_name

FROM [@DB_Name].[sys].[database_principals] u

INNER JOIN [master].[sys].[server_principals] l

ON u.[sid] = l.[sid]

WHERE u.[name] = '@youruser'

I am sure that the developer you are referring to, was already a part of db_denydatareader and db_denydatawriter.

Blog
http://saveadba.blogspot.com/

savethytrees SSCommitted Points: 1968 More actions · Answer 12

It is also listed in on SQL Server central

http://qa.sqlservercentral.com/scripts/Maintenance+and+Management/31598/

Blog
http://saveadba.blogspot.com/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

savethytrees (1/19/2012)
II am sure that the developer you are referring to, was already a part of db_denydatareader and db_denydatawriter.

Someone had to change the permission later this afternoon because he was in one of the databases today.

My appologies.:sick::

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

Lowell (1/19/2012)
I'll look for or create a script for linked servers;
the problem is the credentials for the remote user, if you identify one, cannot be scripted out, because they are part of a CREDENTIAL object;
i did see a blog post about doing it via powershell, but i don't know if the password issue occurs there yet, either.
you can do it one by one via the GUI:
For Jobs,
from another post that just happened today, i just tested this and it ran fine:
http://qa.sqlservercentral.com/Forums/Topic1238602-1550-1.aspx
/**************************************Script to generate all the Jobs on a server **************************************/
--CreatedBy - Amit Mathur (v-amat)
--CreatedDate - 08/26/2009
/**************************************Script to generate all the Jobs on a server **************************************/
SET NOCOUNT ON
BEGIN TRY
PRINT 'USE [msdb]'
PRINT 'GO'
PRINT ''
DECLARE @JobID nvarchar(100),
@JobName varchar (128),
@JobCategory varchar (128),
@JobCategoryClass varchar(128),
@Now datetime,
@Nowtext varchar(30)
SELECT @Now = GETDATE()
SELECT @Nowtext = CAST(@Now as varchar(30))
CREATE TABLE #Jobs (id int identity (1,1), jobid varchar(50))
INSERT INTO #Jobs (jobid) SELECT jobid = convert(varchar(50),job_id) FROM msdb.dbo.SysJobs WITH (NOLOCK)
DECLARE @MaxJobs int,
@JobControl int
SELECT @JobControl = 1
SELECT @MaxJobs = MAX(id) FROM #jobs
--Create Jobs by looping through all the existing jobs on the server
WHILE (@JobControl <= @MaxJobs)
BEGIN --BEGIN Jobs
SELECT @JobID = JobID FROM #jobs WHERE id = @JobControl
SELECT @JobName = name FROM msdb.dbo.sysjobs_view WHERE Job_ID = @JobID
SELECT @JobCategory = sc.name, @JobCategoryClass = category_class FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.syscategories sc
ON sc.category_id = sj.category_id
WHERE Job_ID = @JobID
PRINT '/****** Object: Job ' + @JobName + ' Script Date:' + @Nowtext + ' ******/'
PRINT 'IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N''' + @JobName + ''')'
PRINT 'EXEC msdb.dbo.sp_delete_job @job_name= N''' + @JobName + ''''+ ', @delete_unused_schedule=1'
PRINT 'GO'
PRINT ''
PRINT '/****** Object: Job ' + @JobName + ' Script Date:' + @Nowtext + ' ******/'
PRINT 'BEGIN TRANSACTION'
PRINT 'DECLARE @ReturnCode INT'
PRINT 'SELECT @ReturnCode = 0'
PRINT '/****** Object: JobCategory ' + QUOTENAME(@JobCategory) + ' Script Date:' + @Nowtext + ' ******/'
PRINT 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name = N''' + @JobCategory + ''' AND category_class = ' + @JobCategoryClass+ ')'
PRINT 'BEGIN'
PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N''JOB'', @type=N''LOCAL'', @name = N''' + @JobCategory + ''''
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT ''
PRINT 'END'
PRINT ''
PRINT 'DECLARE @jobId BINARY(16)'
PRINT ''
DECLARE @enabled int,
@notify_level_eventlog int,
@notify_level_email int,
@notify_level_netsend int,
@notify_level_page int,
@delete_level int,
@description nvarchar(128),
@category_name nvarchar(128),
@owner_login_name nvarchar(128),
@notify_email_operator_name nvarchar(128)
SELECT @enabled = sj.enabled,
@notify_level_eventlog = sj.notify_level_eventlog,
@notify_level_email = sj.notify_level_email,
@notify_level_netsend = sj.notify_level_netsend,
@notify_level_page = sj.notify_level_page,
@delete_level = sj.delete_level,
@description = sj.[description],
@category_name = sc.name,
@owner_login_name = SUSER_NAME(sj.owner_sid),
@notify_email_operator_name = so.name
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.syscategories sc
ON sc.category_id = sj.category_id
LEFT OUTER JOIN msdb.dbo.sysoperators so
ON sj.notify_email_operator_id = so.id
WHERE Job_ID = @JobID
PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N''' + @JobName + ''','
PRINT ' @enabled=' + CAST(@enabled as varchar(30))+ ','
PRINT ' @notify_level_eventlog=' + CAST(@notify_level_eventlog as varchar(30))+ ','
PRINT ' @notify_level_email=' + CAST(@notify_level_email as varchar(30))+ ','
PRINT ' @notify_level_netsend=' + CAST(@notify_level_netsend as varchar(30))+ ','
PRINT ' @notify_level_page=' + CAST(@notify_level_page as varchar(30))+ ','
PRINT ' @delete_level=' + CAST(@delete_level as varchar(30))+ ','
PRINT ' @description=N''' + REPLACE(@description, '''','''''') + ''','
PRINT ' @category_name=N''' + @category_name + ''','
PRINT ' @owner_login_name=N''' + ISNULL(@owner_login_name,'sa') + ''','
IF @notify_email_operator_name IS NOT NULL
BEGIN
PRINT ' @notify_email_operator_name=N''' + @notify_email_operator_name + ''', @job_id = @JobID OUTPUT'
END
ELSE
BEGIN
PRINT ' @job_id = @JobID OUTPUT'
END
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT ''
--CREATE STEPS
DECLARE @MaxSteps int,
@LoopControl int
SELECT @LoopControl = 1
SELECT @MaxSteps = MAX(step_id) FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID
WHILE (@LoopControl <= @MaxSteps)
BEGIN
DECLARE @step_name nvarchar (128),
@step_id int,
@cmdexec_success_code int,
@on_success_action int,
@on_success_step_id int,
@on_fail_action int,
@on_fail_step_id int,
@retry_attempts int,
@retry_interval int,
@os_run_priority int,
@subsystem nvarchar (128),
@command nvarchar (max),
@database_name nvarchar(128),
@flags int
SELECT @step_name = step_name,
@step_id = step_id,
@cmdexec_success_code = cmdexec_success_code,
@on_success_action = on_success_action,
@on_success_step_id = on_success_step_id,
@on_fail_action = on_fail_action,
@on_fail_step_id = on_fail_step_id,
@retry_attempts = retry_attempts,
@retry_interval = retry_interval,
@os_run_priority = os_run_priority,
@subsystem = subsystem,
@command = command,
@database_name = database_name,
@flags = flags
FROM msdb.dbo.sysjobsteps WHERE Job_ID = @JobID
AND step_id = @LoopControl
PRINT ''
PRINT '/****** Object: Step ' + @step_name + ' Script Date: ' + @Nowtext + '******/'
PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N''' + @step_name + ''','
PRINT ' @step_id=' + CAST(@step_id as varchar(30))+ ','
PRINT ' @cmdexec_success_code=' + CAST(@cmdexec_success_code as varchar(30))+ ','
PRINT ' @on_success_action=' + CAST(@on_success_action as varchar(30))+ ','
PRINT ' @on_success_step_id=' + CAST(@on_success_step_id as varchar(30))+ ','
PRINT ' @on_fail_action=' + CAST(@on_fail_action as varchar(30))+ ','
PRINT ' @on_fail_step_id=' + CAST(@on_fail_step_id as varchar(30))+ ','
PRINT ' @retry_attempts=' + CAST(@retry_attempts as varchar(30))+ ','
PRINT ' @retry_interval=' + CAST(@retry_interval as varchar(30))+ ','
PRINT ' @os_run_priority=' + CAST(@os_run_priority as varchar(30))+ ', @subsystem=N''' + @subsystem + ''','
PRINT ' @command=N''' + REPLACE(@command, '''','''''') + ''','
PRINT ' @database_name=N''' + @database_name + ''','
PRINT ' @flags=' + CAST(@flags as varchar(30))
PRINT ''
SELECT @LoopControl = @LoopControl + 1
END -- End Steps While
PRINT ''
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT 'EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1'
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT ''
--CREATE SCHEDULES
DECLARE @MaxSchedules int,
@SchedulesLoopControl int
SELECT @SchedulesLoopControl = 1
CREATE TABLE #Schedules (id int identity (1,1), schedule_id int)
INSERT INTO #Schedules (schedule_id) SELECT schedule_id = sjs.schedule_id
FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)
--INNER JOIN msdb.dbo.sysschedules ss WITH (NOLOCK) ON sjs.schedule_id = ss.schedule_id
WHERE sjs.Job_ID = @JobID
SELECT @MaxSchedules = MAX(id) FROM #Schedules
IF EXISTS (SELECT COUNT(*) FROM #Schedules)
BEGIN
WHILE (@SchedulesLoopControl <= @MaxSchedules)
BEGIN
DECLARE @name nvarchar(2000),
@sch_enabled int,
@freq_type int,
@freq_interval int,
@freq_subday_type int,
@freq_subday_interval int,
@freq_relative_interval int,
@freq_recurrence_factor int,
@active_start_date int,
@active_end_date int,
@active_start_time int,
@active_end_time int,
@schedule_uid nvarchar (50)
SELECT @name = name,
@sch_enabled = enabled,
@freq_type = freq_type,
@freq_interval = freq_interval,
@freq_subday_type = freq_subday_type,
@freq_subday_interval = freq_subday_interval,
@freq_relative_interval = freq_relative_interval,
@freq_recurrence_factor = freq_recurrence_factor,
@active_start_date = active_start_date,
@active_end_date = active_end_date,
@active_start_time = active_start_time,
@active_end_time = active_end_time,
@schedule_uid = schedule_uid
FROM msdb.dbo.sysjobschedules sjs WITH (NOLOCK)
INNER JOIN msdb.dbo.sysschedules ss WITH (NOLOCK) ON sjs.schedule_id = ss.schedule_id
INNER JOIN #Schedules s ON ss.schedule_id = s.schedule_id
WHERE sjs.Job_ID = @JobID
AND s.id = @SchedulesLoopControl
PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N''' + REPLACE(@name, '''','''''') + ''','
PRINT ' @enabled=' + CAST(@sch_enabled as varchar(30))+ ','
PRINT ' @freq_type=' + CAST(@freq_type as varchar(30))+ ','
PRINT ' @freq_interval=' + CAST(@freq_interval as varchar(30))+ ','
PRINT ' @freq_subday_type=' + CAST(@freq_subday_type as varchar(30))+ ','
PRINT ' @freq_subday_interval=' + CAST(@freq_subday_interval as varchar(30))+ ','
PRINT ' @freq_relative_interval=' + CAST(@freq_relative_interval as varchar(30))+ ','
PRINT ' @freq_recurrence_factor=' + CAST(@freq_recurrence_factor as varchar(30))+ ','
PRINT ' @active_start_date=' + CAST(@active_start_date as varchar(30))+ ','
PRINT ' @active_end_date=' + CAST(@active_end_date as varchar(30))+ ','
PRINT ' @active_start_time=' + CAST(@active_start_time as varchar (30)) + ','
PRINT ' @active_end_time=' + CAST(@active_end_time as varchar (30)) + ','
PRINT ' @schedule_uid=N''' + @schedule_uid + ''''
PRINT ''
PRINT ''
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT ''
SELECT @SchedulesLoopControl = @SchedulesLoopControl + 1
END -- End Schedules While loop
END -- END IF (SELECT COUNT(*) FROM #Schedules) > 0
DECLARE @server_name varchar(30)
SELECT @server_name = CASE server_id WHEN 0 THEN 'local' ELSE 'Multi-Server' END
FROM msdb.dbo.sysjobservers WHERE Job_ID = @JobID
PRINT 'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name =N''(' + @server_name + ')'''
PRINT 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
PRINT 'COMMIT TRANSACTION'
PRINT 'GOTO EndSave'
PRINT 'QuitWithRollback:'
PRINT ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'
PRINT 'EndSave:'
PRINT ''
PRINT 'GO'
PRINT ''
PRINT ''
SELECT @JobControl = @JobControl + 1
DROP TABLE #Schedules
END --End Jobs
DROP TABLE #Jobs
END TRY
BEGIN CATCH
DROP TABLE #Jobs
DROP TABLE #Schedules
END CATCH;

Lowell,

I get errors on the lines that have this statement (3):

WHILE (@JobControl <= @MaxJobs)

If you already responded, I apologize.

I'm still trying to script the maintenace jobs.

I tried restoring MSDB with the following.:w00t:

RESTORE DATABASE MSDB

FROM DISK = 'msdb_backup_201201181321.bak'

WITH REPLACE,

MOVE 'MSDB' TO 'D:\SQLServer\Data\MSDB.mdf',

MOVE 'MSDB_log' TO 'D:\SQLServer\Log\MSDB.ldf'

Msg 3201, Level 16, State 2, Line 1

Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\msdb_backup_201201181321.bak'. Operating system error 2(The system cannot find the file specified.).

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/