Recover unsaved SQL queries from SQL Server Management Studio (SSMS)

Summary

If you're running SQL Server Management Studio (SSMS) and it crashes and you lose all of your unsaved queries, it can be rather frustrating, to put it mildly.

If you're already using a tool like the excellent (and free) SSMSBoost or the excellent (and inexpensive - €30 for a single computer license) SSMS Tools Pack or the excellent (and rather more expensive - £225 before any discounts) SQL Prompt from RedGate then, provided you have already configured the tool to save your queries, you can easily recover the lost queries.

Incidentally, those three tools are not the same (otherwise nobody would use the ones that aren't free). It's just that, amongst other things, they each happen to offer the kind of functionality we're interested in. My advice is that you should try each of them for yourself and then decide if either of the paid offerings is worth the money. There are some SQL Prompt tips on YouTube which show you a few of the things it can do.

If you haven't already installed any of these tools (or you have, but haven't configured it/them to save your history) then there is still a way to find the lost data, but it can be a bit painful.

In this article, I present a way to make it significantly less painful. This is exactly what I did when I found myself in this situation a couple of weeks ago and it worked for me.

Background

The other day I was analysing some data in SQL Server Management Studio (SSMS) when my computer locked up and, eventually, SSMS crashed. I had lots of unsaved queries open and when SSMS restarted, it had helpfully recovered only the single query which I had already saved.

I knew that I had previously run the queries I needed, but I also knew I hadn't saved them.

Since I was using SSMS 2014 and had set Tools → Options → Environment → AutoRecover to save every five minutes, I thought I'd be okay... but I wasn't.

I also tried looking in C:\Users\MyUsername\Documents\SQL Server Management Studio\Backup Files and C:\Users\MyUsername\AppData\Local\Temp, but to no avail.

Eventually I found a snippet of SQL which would retrieve the queries for me, but every time I tried to save the recovered queries the data was truncated, regardless of whether I tried Results to Grid, Results to Text, Results to File and regardless of how I had configured the maximum lengths under Tools → Options → Query Results → SQL Server. I remembered that I'd met this issue before (you probably have too) and that the only way to ensure that the data wasn't truncated was to convert it into XML. This is quite frustrating, but everything I found seemed to confirm that this is indeed still the case.

If you're thinking [processing-instruction(x)], don't worry, I'll be mentioning that shortly.

If we accept that we need to convert the data to XML then we are left with the choice of whether to try to process that XML data in SSMS, or to do so via some other means. I opted to write a little bit of C# to do the job.

Don't worry if you have neither Visual Studio nor LINQPad installed on your machine. You can easily download a standalone version of LINQPad from http://www.linqpad.net/download.aspx and paste my code into it without buying a license. Try it and see.

Retrieving the unsaved queries

First set your XML size limit to a suitable value in Tools → Options → Query Results → SQL Server → Results to Grid, like this:

Now run this script in SSMS, noting the lines which are commented out, one of which (or a variation thereof) you may want to reinstate:

NOTE: You need VIEW SERVER STATE permission on the server to do this.

USE [DBName]
SELECT execquery.last_execution_time AS RunDate, 'DBName' AS DB, execsql.text AS Script
INTO #queries
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
--WHERE execquery.last_execution_time < '2015-10-12'
--WHERE execquery.last_execution_time BETWEEN '2015-10-14' AND '2015-10-15'
--WHERE CAST(execquery.last_execution_time AS DATE) = '2015-10-15'

Now run this script in the same window:

SELECT DB, Script, CONVERT(NVARCHAR, Max(RunDate), 120) RunDate
FROM #queries
WHERE DB='DBName'
GROUP BY DB, Script -- this removes duplicate entries, keeping only the most recent version
--HAVING MAX(RunDate) > '2015-10-15' -- again, you can filter here like this
ORDER BY RunDate DESC
FOR XML PATH('Query'), ROOT('Queries'), ELEMENTS

You will note that we are recording the database name. That way we could add more queries from other databases into our temporary table before we extract them by changing the query as shown below:

SELECT execquery.last_execution_time AS RunDate, 'DBName' AS DB, execsql.text AS Script
INTO #queries
-- rest of query...
-- BECOMES
INSERT INTO #queries (RunDate, DB, Script)
SELECT execquery.last_execution_time AS RunDate, 'DBName' AS DB, execsql.text AS Script
-- rest of query...

We could have combined the lost query retrieval and conversion to XML into one clever script, but this way it's easier to see what's going on. And I wanted to be honest and give you the exact code I ran, so here it is, bereft of finesse as it may be.

Now click on the blue hyperlink in the results grid in SSMS and save the resultant output with Ctrl-S. It will choose a filename something like XML_F52E2B61-18A1-11d1-B105-00805F49916B17.xml.

NOTE: I'm aware that giving a column an alias of [processing-instruction(x)] prevents the XML from doing things like converting > to >, but it also incorporates the value in parentheses into the XML which is slightly irksome to me. And since I am going to do the final extraction using C# code, the escaping of the characters doesn't matter to me as the conversion back from things like > to > will happen automatically.

If you want to see what I'm on about, change SELECT DB, Script, CONVERT(... above to be SELECT DB, Script as [processing-instruction(x)], CONVERT(..., but change it back again before you proceed.

You could stop at this point and find your script in the single large text file you just saved, but I wanted to make life a bit easier, so I wrote some code to split the XML file into a separate file for each query.

Splitting the unsaved queries into separate files with sensible names

Now run this very simple script in LINQPad, putting the filename (and path) to the file you just saved in the obvious place.

If you haven't already got LINQPad, you should definitely consider checking it out. I bought a license over five years ago and I wrote about using it with NHibernate quite recently. Paying for a license activates the intellisense autocomplete functionality, but you don't actually need that for this as I have already written the code for you.

void Main()
{
	var basePath = @"C:\temp" + Path.DirectorySeparatorChar;
	var path = basePath + "XML_F52E2B61-18A1-11d1-B105-00805F49916B17.xml";
	var savePath = basePath + "Recovered" + Path.DirectorySeparatorChar;
	Directory.CreateDirectory(savePath);
	CreateFiles(path, savePath);
}

private void CreateFiles(string path, string savePath)
{
	var xElem = XElement.Load(path);
	var scripts = xElem.XPathSelectElements("//Query");
	
	int i=1;
	foreach (var script in scripts)
	{
		var text = script.XPathSelectElement("Script").Value;
		var db = script.XPathSelectElement("DB").Value;
		var dateTimeSuffix = script.XPathSelectElement("RunDate").Value.Replace(' ', '_').Replace("-", "").Replace(":","");
		var currentPath = savePath + db + "_" + i++.ToString("00000") + "_" + dateTimeSuffix + ".sql";
		File.WriteAllText(currentPath, text);
	}
}

It will generate a load of files with names like this:

DBName_00001_20151016_060000.sql
DBName_00002_20151016_055950.sql
...
DBName_nnnnn_YYYYMMDD_HHMMSS.sql

Finally, you can look through these files and try to find your missing queries. I used the search feature built into Notepad++ for this purpose and it took me about two minutes to find what I was looking for.

Conclusion

In my experience, whilst it is undoubtedly superb, SSMS can be a capricious beast.

The best way to make sure you don't lose the queries that you're working on is to save your work frequently. The next best way is to install SSMSBoost (free), SSMS Tools Pack (not free, but has a 60-day demo license) or SQL Prompt (not free, but has a 14-day trial) and enable history tracking.

If that's not an option for you, either due to corporate software restrictions or the fact that you haven't yet installed one of those tools, then you can try to retrieve your data using the advice given above.