September 23, 2020 at 9:00 pm
Is there a way to capture 2 things, when did the job failed and why did it fail ( capturing error message). I don't have the history available for couple of jobs but do know they have failed before and they were fixed. I want to know why they failed before like by looking at the various errors or failed instances for like a year.
September 24, 2020 at 12:56 pm
So you need to go back a year? I would see what there is in msdb.dbo.sysjobhistory
September 24, 2020 at 3:14 pm
year was just an example, I would say as long as I can go back if that is possible. Not more than a year for sure.
September 24, 2020 at 5:55 pm
If the history isn't available in sysjobhistory you'd have to do something like find old backups of msdb that would have the history and restore them on a test, dev, sandbox or whatever server (using a different database name) . But for system databases, the server you restore to needs to be the same version, build number as the backup so it can be a bit of a challenge. And then finding the correct older msdb, if it exists...it can all be quite a bit of work.
Sue
September 25, 2020 at 3:55 pm
Restore old msdb backup(s) to a different db name. We typically add '_' and the date of the backup to the name. Something like this:
RESTORE DATABASE msdb_20190915 FROM DISK = 'x:\full\path\to\backup\folder\msdb.bak' WITH MOVE 'MSDBData' TO 'n:\full\path\of\where\to\put\restored\datafile\MSDBData_20190915.mdf', MOVE 'MSDBLog' TO 'l:\full\path\of\restored\logfile\MSDBLog_20190915.ldf'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply