Getting Our Query Back After SSMS Crashed or We Closed the Tab Without Saving It

Here’s a quick one for you, have you ever spent all day working on some query or another only for SSMS to crash or for you to accidentally close the tab that you’re working on without saving the query?

I’m sure you have, we all have. What happens next is usually a muttering of some very choice words.

I’ve had this happen to me more than once so I thought I’d share a little tip on how to get back that script that you’ve lost somewhere in the ether.

Now I’m going to assume that you’ve been working on a development box that that you’ve been running this thing to test periodically as you’ve been writing it.

If you have been working like that, then there is a special place that all your hard work might well still be lurking and that’d in the plan cache.

It’s a pretty simple job to dip into the plan cache and search for your query. Take a look at the query below, inserting a string that you know was part of the lost script into the WHERE clause.

SELECT SQLText.text
FROM sys.dm_exec_cached_plans plans
OUTER APPLY sys.dm_exec_sql_text(plans.plan_handle) SQLText        
WHERE SQLText.text LIKE '%<some text that we know was in the script>%'

Lets See It In Action…

So lets see this in action. I’ve spent all day working on a very complex query to query the Undercover Catalogue and give me a list of all the sysadmins on my servers. (Ok, it’s not all that complex but you’ll get the point for the sake of this little demo)

SELECT ServerName, LoginName, RoleName,LastRecorded
FROM Catalogue.Logins
WHERE RoleName = 'sysadmin' 
AND IsDisabled = 0
AND LastRecorded > (SELECT MAX(ExecutionDate)
					FROM Catalogue.ExecutionLog
					WHERE CompletedSuccessfully = 1)

But what?! Opps, I’ve just closed the tab and forgotten to save it, what do I do?

Well, knowing my little trick, I can take a peep into the plan cache and see if I can find anything in there…

I know that I was querying the Catalogue.Logins table, so I’ll use that to search for.

SELECT SQLText.text
FROM sys.dm_exec_cached_plans plans
OUTER APPLY sys.dm_exec_sql_text(plans.plan_handle) SQLText        
WHERE SQLText.text LIKE '%Catalogue.Logins%'

Run that and lets see what comes back…

And there is it, I’ve got my script back. 🙂