July 8, 2009 at 7:12 am
I have a situation where I had two SQL Server agent jobs scheduled to run daily. The jobs are a bit extensive with several steps. One of the jobs has gotten deleted. How do I restore this job without losing my other job? I do have a valid backup of my MSDB database.
July 8, 2009 at 8:18 am
this is one reason why we script all our jobs on a weekly basis :Whistling:
What you could do is : ( ON A test SERVER IF YOU CAN ! )
- shut down SQLAgent.
- make a new backup of msdb (to a new backup file !!!)
- restore the pervious msdb bacup
- activate the agent xp's
-- enable Agent XPs
EXEC sp_configure 'Agent XPs','1'
RECONFIGURE
- script your job
- restore the newly created msdb backup
- implement the job from the script in the pervious step
- start sqlagent
Actually , you can restore msdb like any other userdb
e.g.
RESTORE DATABASE [msdb_rest] FROM DISK =
N'X:\MSSQL.1\MSSQL\Backup\msdbFull.BAK'
WITH FILE = 1
, MOVE N'MSDBData' TO N'X:\MSSQL.1\MSSQL\DATA\msdb_rest.mdf'
, MOVE N'MSDBLog' TO N'X:\MSSQL.1\MSSQL\DATA\msdb_rest_1.ldf'
GO
Keep in mind you are tampering with system stuff !
This is not advized !!
So you could pull over the data of the missing job this way
insert into msdb.dbo.sysjobs
SELECT *
from msdb_rest.dbo.sysjobs J
where J.name ='DBA_Db_Rebuild_Reindex'
go
insert into msdb.dbo.sysjobsteps
Select JS.*
from msdb_rest.dbo.sysjobsteps JS
inner join msdb_rest.dbo.sysjobs J
on J.job_id = js.job_id
where J.name ='DBA_Db_Rebuild_Reindex'
go
With the above steps, you will have the job and its steps.
You will not yet have the schedule, ...
Play it safe, start with a full backup
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2011 at 11:57 am
How to restore it's schedules?
December 16, 2011 at 2:06 pm
of course this restored db also holds the sysjobschedules table ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2011 at 2:09 pm
I am asking how to restore all schedules to the restored Job to function as it was before?
December 16, 2011 at 2:18 pm
- What did you try ?
- I think it may work like the other queries in this list, don't you ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 4, 2012 at 4:42 pm
I tried and it worked.
Thank you for sharing.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply