August 3, 2018 at 9:33 am
Hi,
I was wondering on our server we have backups of our databases for course, but what can I do to save the jobs?
know I can script them out and save that, which I guess would recreate them, but is that what I should do or is there a better way?
Then what about other things like views and triggers?
And if that is the was to go is there a way to do them all at once?
Thank you
August 3, 2018 at 9:54 am
In SSMS, you can click on the jobs folder of the Object Explorer (press the F8 key to get there if you don't already know how) and then press the F7 key. That'll bring up a details screen with all the jobs in it. Select the ones you want and then right click on one of them... it'll be obvious from there.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2018 at 10:00 am
Jeff Moden - Friday, August 3, 2018 9:54 AMIn SSMS, you can click on the jobs folder of the Object Explorer (press the F8 key to get there if you don't already know how) and then press the F7 key. That'll bring up a details screen with all the jobs in it. Select the ones you want and then right click on one of them... it'll be obvious from there.
When I script Agent Jobs that have a schedule I always delete the @schedule_uid value.USE [msdb]
GO
/****** Object: Job [Test] Script Date: 03/08/2018 16:58:27 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 03/08/2018 16:58:27 ******/
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=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'DESKTOP-JJDHQLS\Jonathan', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Step1] Script Date: 03/08/2018 16:58:28 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1',
@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 * FROM INFORMATION_SCHEMA.TABLES',
@database_name=N'Test',
@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'SheduleTest',
@enabled=1,
@freq_type=8,
@freq_interval=3,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20180803,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'54f035e7-d4c4-4e97-af80-23c9eda520fa'
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
August 3, 2018 at 10:09 am
Thanks this really helpful so you save the scripts.
What can I do to save view, triggers, security, objects and other things like that, incase my server goes down ?
Thanks
August 3, 2018 at 11:06 am
itmasterw 60042 - Friday, August 3, 2018 10:09 AMThanks this really helpful so you save the scripts.
What can I do to save view, triggers, security, objects and other things like that, incase my server goes down ?
Thanks
In the Object Explorer, right click on a database you want to generate scripts for. Then select {Tasks} and then {Generate Scripts}. Follow your nose after that.
Shifting gears a bit, while I appreciate the great precautions you're taking, they're a sore substitute for the proper use of source control and all the things that go with it including not allowing people to deploy their own code to production. The source control side of it doesn't need to be as complicated as some folks make it with a bazillion branches and all that. And nothing should move to production without reviewed scripts being checked into source control after they're been fully tested by the Developers, QA, and UAT. There will always be the production emergency that needs to be resolved and those should take similar steps at a more accelerated rate. One of the cool parts of all this is that the process will make it so that there actually are fewer "production emergencies" and much fewer mistakes during deployment time.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply