Recover the lost query

Just recently I was writing a very complicated query. Took me 2 hours to write. In the end I saved it as a view in the development database. I did not yet synchronize it back to the solution in visual studio, because I wanted to add a few related views for the problem I was solving. Maybe it was better to develop from visual studio, but sometimes you just want to see the results from the query, and merge it back to visual studio later.

At that moment however, a colleage of mine did a publish from his version of Visual Studio to the database on the development server. Gone were all my views and my hard work. 🙁 Lessons learned of course. But then I felt a mission. The mission to find my lost query. I would rather try two hours to recover my query than to rewrite it from scratch. In the end I found this gem:

(taken from:

select query.last_execution_time, 
sql.text [sql] 
FROM sys.dm_exec_query_stats query
cross apply sys.dm_exec_sql_text(query.sql_handle) [sql]
order by 1 desc

And I did find my lost query!

So if you ran a query to your SQL database and accidently closed the session, you might want to try this to find it back.

Strangely enough I did not find the alter view statement, but I did find my select statement. So you might have to search a little. But at least there is hope.

Principal BI consultant at Rubicon

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next ArticleRow level security on your SQL database