Recovering an unsaved SSMS Query

This is a something I deal with from time-to-time; yesterday, most recently, as a matter of fact.

So, you have been in SQL Server Management Studio (SSMS) all morning working on that query when the office lights flicker, then…. Darkness! Your monitor turns off and you hear your PC fans go quiet. You just lost power but, even worse, you just lost that query.

You know that you need to save your work as often as possible (and usually do) but now is not the time for that advice. Is there any way to get that work back?

Here's how the Ninja does it:

Steps to recovery an unsaved query created in SSMS

1) Go to Start

2) Go into the Search programs and files box

3) Search for this (exactly as shown):

"~AutoRecover.

Your work should appear under Documents and is saved as ~autoRecover.{something}.sql

4) Right-click on one of the “~autoRecover” files and select Open File Location.

5) Your SQL files should be in that folder.

At this point you can open them and save them as a different name.

Behind the scene:

Starting with version 2005, SQL Server includes an Auto Recovery feature which automatically saves your work. It behaves like the Auto Recovery feature in Microsoft Word where, if your system should crash or hang, you can retrieve the files from their recovered state.

This feature in SQL Server is not configurable via the GUI;it can only (to my knowledge) be enabled/disabled in the registry under:

2008: HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\General\AutoRecover
2005: HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover

Last Updated: May 1, 2013 (formatting)