Did you ever accidentally close SQL Server Management Studio? And, in closing SSMS, did you get the prompt that says “Save changes to the following items?” And did you, completely unthinkingly, with a query you had just been working on, hit Cancel? Yeah, me neither. What kind of idiot does that….

OK. I confess. I just did that. Silly thing it was, but I had just spent at least 1/2 an hour working on a query and now it was gone…. or was it? I had just run the query and had been looking at the results when I closed SSMS. Initially, I panicked and started thinking about how I could get the data back (somewhere there’s a file I’ve heard). Then it occurred to me, I had just been writing queries against the cache using DMOs. Why don’t I just pull it using the DMOs I had just been using? Worked like a charm.

SELECT  dest.text
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 > '5/19/2011 11:00'
        AND dest.text LIKE 'WITH%';

My query was much more complicated, but this was all I needed. I was able to filter out the junk in cache by only selecting stuff with an execution time within about 15 minutes of when I had closed SSMS, and I supplied the start of the query, a CTE. That was all I needed. I got back my query. Took a little formatting work, but it was immediately available and all was right with the world.

Just posting this in case you hit the same situation. You too can get your query back, easily and quickly. Then again, maybe I’m the only one stupid enough to do that.