2 Replies Latest reply on Sep 10, 2014 2:41 PM by Carl.Simpson

    Emergency Management of Service Desk


      Yesterday I killed our production database by deleting an object that was scheduleable.  For the record, don't do that.  Support advised me that my database was trashed and to restore my backup from last night.  Pretty standard stuff.  Then something interesting happened, my DBA suggested that he could copy my database to new instance so I could still access the data.  That never occurred to me, I thought all was lost.  I could not launch Service Desk or anything useful but my data still existed even though the meta data was a huge mess.  I wrote a few scripts that I ran against my old database.  I ran the scripts below all at once and had 5 windows in SQL.  I copied all the incidents from the first select statement to Excel and then removed the comment before "and im_id = @ID".  Then for each incident on the spreadsheet I updated @ID (my incident number) and ran the script.  I had the basic data for creating the incident, the resolutions (if any) for that incident, the analyst notes (notes only an analyst can see), the notes, and then the assignments.  With that data I could go back and easily put all of my data back the way it was.  Obviously my attributes are different than yours but I think you should get the idea of what is going on.


      You will want to try this NOW and modify it while all is well so what that dreadful day does come, this query will be ready to get you going again and reduce some of the damage.  For me it was a slow day and I noticed the corruption before noon so it could have been a lot worse.  Next time I will know exactly what to do and won't waste a bunch of time writing these scripts.  I hope this saves someone a lot of effort some day.



      declare @ID Integer = 201427957, @BadDate datetime = '9/9/2014'


      --New Incidents

      SELECT im_incident.im_id, 'Incident', usr_poc, tps_user_2.tps_name AS Creator, tps_user.tps_name AS [End User], pm_process.pm_description, pm_process.usr_description_html, pm_process.pm_creation_date

      FROM ((im_incident INNER JOIN pm_process ON im_incident.pm_guid = pm_process.pm_guid) INNER JOIN tps_user AS tps_user_2 ON pm_process.pm_creation_user_guid = tps_user_2.tps_guid) INNER JOIN tps_user ON pm_process.pm_raise_user_guid = tps_user.tps_guid

      WHERE (((pm_process.pm_creation_date)>= @BadDate)) --and im_id = @ID

      ORDER BY im_incident.im_id;



      SELECT im_incident.im_id, im_incident_resolution.im_creation_date, tps_user_2.tps_name AS [Resolved By], im_incident_resolution.usr_description2_html, im_incident_resolution.usr_description2

      FROM (im_incident LEFT JOIN im_incident_resolution ON im_incident.pm_guid = im_incident_resolution.im_incident_guid) LEFT JOIN tps_user AS tps_user_2 ON im_incident_resolution.im_creation_user_guid = tps_user_2.tps_guid

      WHERE (((im_incident_resolution.im_creation_date)>= @BadDate)) and im_incident.im_id = @ID

      ORDER BY im_incident_resolution.im_creation_date;


      --Analyst Notes

      SELECT im_incident.im_id, 'Analyst Note', usr_analystnote.usr_description, usr_analystnote.usr_description_html, usr_analystnote.usr_creationdate, tps_user_2.tps_name

      FROM (im_incident LEFT JOIN usr_analystnote ON im_incident.pm_guid = usr_analystnote.usr_incident) LEFT JOIN tps_user AS tps_user_2 ON usr_analystnote.usr_creationuser = tps_user_2.tps_guid

      WHERE (((usr_analystnote.usr_creationdate)>= @BadDate)) and im_id = @ID

      ORDER BY im_incident.im_id;



      SELECT im_incident.im_id, 'Note', im_incident_note.im_text, im_incident_note.usr_text_html, im_incident_note.im_creation_date, tps_user_1.tps_name

      FROM (im_incident LEFT JOIN im_incident_note ON im_incident.pm_guid = im_incident_note.im_incident_guid) LEFT JOIN tps_user AS tps_user_1 ON im_incident_note.im_creation_user_guid = tps_user_1.tps_guid

      WHERE (((im_incident_note.im_creation_date)>= @BadDate)) and im_id = @ID

      ORDER BY im_incident.im_id;



      SELECT im_incident.im_id, 'Assignment', pm_process_assignment.pm_creation_date, pm_process_assignment.pm_description, tps_user.tps_name AS Creator, tps_user_1.tps_name AS [Assigned User], tps_group.tps_name AS [Assigned Group], pm_process_assignment.pm_serial_number

      FROM (((((im_incident INNER JOIN pm_process ON im_incident.pm_guid = pm_process.pm_guid) INNER JOIN im_incident_assignment ON pm_process.pm_guid = im_incident_assignment.im_incident_guid) LEFT JOIN pm_process_assignment ON im_incident_assignment.pm_guid = pm_process_assignment.pm_guid) LEFT JOIN tps_user ON pm_process_assignment.pm_creation_user_guid = tps_user.tps_guid) LEFT JOIN tps_user AS tps_user_1 ON pm_process_assignment.pm_user_guid = tps_user_1.tps_guid) LEFT JOIN tps_group ON pm_process_assignment.pm_group_guid = tps_group.tps_guid

      WHERE (((pm_process_assignment.pm_creation_date)>= @BadDate)) and im_id = @ID

      ORDER BY im_incident.im_id, pm_serial_number;