Corrupt T-Sql Script

• ldorian81

  SSCommitted

  Points: 1694

  More actions

  January 16, 2012 at 4:40 am

  #144872

  Hi All,

  I've wrote a T-Sql script last week, that this morning has decided to corrupt.

  I know that I definately ran it friday... is there anyway to retrieve it using T-SQL?

  I tried...

  SELECT

  deqs.last_execution_time AS [Time],

  dest.TEXT AS [Query]

  FROM

  sys.dm_exec_query_stats AS deqs

  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

  Where

  (deqs.last_execution_time >= '2012-01-16 15:30:000' And deqs.last_execution_time <= '2012-01-16 17:10:000')

  ORDER BY

  deqs.last_execution_time DESC

  But can't see it... Anyone got any ideas?

  Regards
  ld

  Stoke-on-Trent
  United Kingdom

  If at first you don't succeed, go to the pub and drink away your current thought plan.

• Lowell

  SSC Guru

  Points: 323504

  More actions

  January 16, 2012 at 5:37 am

  #1434011

  without any auditing already in place, all you can do is prepare for it to happen again in the future.

  http://www.ssmstoolspack.com/ has the free SSMS toolpack, and its "SQL History" feature has saved me once, in a similar situation: long script of code i was working on wasn't saved, then accidentally closed the window on it., but I was able to go to the history and recover it a couple of days later.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Grant Fritchey

  SSC Guru

  Points: 398694

  More actions

  January 17, 2012 at 4:59 am

  #1434509

  Queries don't stay available in the cache forever. If you ran it once and it had a low cost, chances are if there's any memory pressure on the system at all, it aged out of cache and it's gone.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning