You are using the query editor in SQL Server Management Studio (SSMS). You have spent an hour writing and tweaking a query. Then probably another 15 minutes to format it to your liking. But you haven’t saved it yet. If the SSMS crashes now before you could save your work, is all of your effort wasted? Maybe not. The in-built auto-recover features make it possible to recover unsaved queries after SSMS crash. I discuss all the manual or tool based recovery options in this article.

Auto-Recovery in SSMS

The SSMS developer team anticipated this situation so they have built in some auto recovery options in the tool. Usually, the next time you start SSMS after a crash, it tries to recover your unsaved queries. The recovered files show up in a dialog box as shown below. The dialog box has a list of files that SSMS was able to recover. You can select the files that you wish to salvage.

The file names are cryptic with no indication about their content. This kind of naming is ok if you were working with only one query window and know which query is being salvaged. But if you had multiple query windows open at the time of crash, the cryptic file names will be of no help. So you might want to recover all the files in this list and review them later. Next, make a note of the folder locations where the files will be recovered so that you can take a look at them. Now you can click the Recover Selected Files button.

What If Auto-Recovery Does Not Work

Sometimes SSMS may not present the recovery dialog box. It might seem that all the work is lost. Well, most of the queries (if not all) can still be recovered because the recovery dialog shows where are the backup files located.

Windows XP

On Windows XP, SSMS saves auto recover copies of queries as you work, in the folder –

C:/Documents and Settings/<user name>/My Documents/SQL Server Management Studio/Backup Files/Solution1

Windows 7

On Windows 7, the queries can be recovered from –

C:/Users/<user name>/Documents/SQL Server Management Studio/Backup Files/Solution1

Recovery Folder Content

The names of the auto saved files are cryptic. So you will need to open all the files to find the one you are interested in.

Other Options

If auto recover does not work help for any reason, then here are a few more options.

SQL Server Query Cache

You can try recovering TSQL statement text using some of the DMVs and DMFs. The sys.dm_exec_requests along with sys.dm_exec_sql_text can show the statement text. But this is helpful only if you executed the query and the query is still in SQL Server’s memory. If the query text was just sitting in the editor and you never executed it before the SSMS crash then this method cannot help.

USE [your_database_name]
GO
SELECT
s.last_execution_time AS [ExecutionTime], 
t.text AS [Statement] 
FROM
sys.dm_exec_query_stats AS s
CROSS APPLY
sys.dm_exec_sql_text(s.sql_handle) AS t
ORDER BY
s.last_execution_time DESC

Third Party Tools

Some third party tools like SSMS Boost (free), Red Gate SQL Prompt (paid after trial), SSMS Tools Pack (paid after trial) etc. can help in a situation like this, but only if you had them installed and running at the time of the crash. They cannot help otherwise.

While talking about tools, you can even up vote this Connect item to add an undo closed tab option in SSMS.

Conclusion and Best Practices

• Save your work frequently. CTRL+S is your friend. This advice is good not just for web designers but software developers too!
• Use a version control system like Git, SVN etc.