February 7, 2007 at 4:05 pm
Hello,
Is there a way to quickly generate script to an existing maintenance plan?
Thank you.
R
February 7, 2007 at 4:07 pm
In 2005 MP are stored as SSIS packages and not system tables so you can't script them but I believe you can copy the SSIS package from some server to another...
MohammedU
Microsoft SQL Server MVP
February 8, 2007 at 6:57 am
We have a MP that does rebuilding index, check database integrity, full backup, transaction log backup etc., But they are all scheduled to run as a group. Is there a way to separate each task to run at different intervals. One way to do will be to separate these jobs and have them scheduled at desired frequency.
But in SQL Server 2000, it's very easy to do the same thing using the wizard.
Any advice?
February 8, 2007 at 4:55 pm
You can't accomplish with one MP configured for all...
You have to create diffent MPs one for each process (backup, checkdb, reindex...)...
OR
You can create a job using tsql without using MP...
I prefer not to use MP...because you will have more control on the process...
MohammedU
Microsoft SQL Server MVP
February 9, 2007 at 10:40 am
Depending on the database, I have the following tasks selected.
Reorganize Index
Rebuild Index
Back Up Database (Full)
...
Something I noticed that is very useful (at least for me) is the way to get the exact code that SQL Server is running behind the scenes.
Create a MP using the wizard and enable DB mail make sure an operator is alerted (via email). So every time the job is run successfully, in the email alert, you can see TSQL commands that SQL Server ran.
Can someone show how to schedule a job using a script.
Thank you.
February 9, 2007 at 5:29 pm
You can create a job and script it..
Any way here is the script...
USE [msdb]
GO
/****** Object: Job [test] Script Date: 02/09/2007 16:28:39 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 02/09/2007 16:28:41 ******/
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'test',
@enabled=1,
@notify_level_eventlog=2,
@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 [t] Script Date: 02/09/2007 16:28:42 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N't',
@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=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'backup database master to disk = ''c:\master.bak'' with init
',
@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_jobschedule @job_id=@jobId, @name=N'test',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20070117,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
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:
MohammedU
Microsoft SQL Server MVP
February 13, 2007 at 3:03 pm
Thanks Mohammed, I figured out how to generate these scripts.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply