August 22, 2016 at 12:52 am
Hello - not sure what happened but our SQL jobs were just disappeared and I don't see any logs either. So, I am not sure if there is a way to recover the jobs that were gone.
Please assist.
Thanks!
August 22, 2016 at 3:48 am
Got a backup of MSDB from before the jobs 'disappeared'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2016 at 9:30 am
We have a backup but that wasn't latest, this isn't a Production box however we use this for Telemetry purpose so not taking regular backups. We have implemented a process now to take regular backups now though 🙂
Is there way to find out which account caused this behavior?
Thanks!
August 22, 2016 at 9:36 am
You can check the default trace, but I'm not sure if job deletions are recorded.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2016 at 5:17 am
it's a good idea to script out your jobs and keep them with your source, although backups are still preferrable
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 23, 2016 at 8:17 pm
Yes, learnt the lesson. Daily backups are being taken now.
Thank You!
August 24, 2016 at 11:56 pm
Jobs are stored in msdb. The normal way to delete them is with calls to msdb.dbo.sp_delete_job.
If you have any idea when this occured, you could take a look at agent's log file to see if anything bad happened. It's in a file called SQLAGENT.OUT, in a location like C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG.
The log file gets renamed to SQLAGENT.1,2,3,4 each time you restart agent, so you might see a few there. OUT is the current one.
Another possiblity is someone changed the owner of the job. A non-sa user won't necessarily be able to see other user's jobs.
You can alos take the help of third party MDF recovery software[/url] to recover SQL jobs.
August 25, 2016 at 5:15 am
johnwalker10 (8/24/2016)
If you have any idea when this occured, you could take a look at agent's log file to see if anything bad happened. It's in a file called SQLAGENT.OUT, in a location like C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG.
Job deletions are not recorded in the SQL Agent error log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply