Recover unsaved queries after SSMS crash or PC restart

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.

The auto recovery dialog will help recover unsaved queries after SSMS crash
SSMS Query Recover Dialog

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.

Recover unsaved queries after SSMS crash in this folder
SSMS Query Auto Recover Location in Windows XP

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]
s.last_execution_time AS [ExecutionTime], 
t.text AS [Statement] 
sys.dm_exec_query_stats AS s
sys.dm_exec_sql_text(s.sql_handle) AS t
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.

6 thoughts on “Recover unsaved queries after SSMS crash or PC restart

  1. sagar March 25, 2013 / 3:52 am

    Tell me what are the setting i need to do on my PC to show the pop up for recover file.

    • Aalam Rangi March 25, 2013 / 9:24 am

      It is an automatic behavior of SSMS. There is no user-tweakable setting. Somehow SSMS is not consistent with the pop-up. Maybe it depends on the way the SSMS is closed. If you see a pop-up then that is great, if not then you know where to look for the files now.

  2. Mangesh May 16, 2013 / 2:15 am

    Very informative!! Saved hours of my rework.. 🙂

  3. events November 15, 2013 / 2:27 am

    Hey, I think your blog might be having browser compatibility issues.

    When I look at your blog in Safari, it looks fine but when opening in Internet Explorer, it has some overlapping.
    I just wanted to give you a quick heads up! Other then that, excellent

  4. Pingback: SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *