SQL Server 2012 Managment Studio not saving auto recovery files

Question

I tried to go to Tools > Environment > AutoRecover.

• The check box of save autorecover is checked.
• Save every "5" minutes.
• Keep autorecover info for: 7 days.

But it doesn't save any auto recover files (I searched inside my user folder for *.sql), and doesn't suggest to auto recover when the Management Studio closes by a power down.

I searched the registry to see if something was changed manually in HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server but only found one key: CustomerFeed.

Answer 1

According to AutoRecover information is not saved, SSMS 2012 only saves recovery files for queries that have been previously saved (which has led to some complaints). Files that have not been saved seem to get dumped in C:\Users\%USERNAME%\AppData\Local\Temp.

So you can check:

• C:\Users\%USERNAME%\Documents\SQL Server Management Studio
• C:\Users\%USERNAME%\AppData\Local\Temp

Personally I use TFS for version control on my queries and save them before every run. I've lost a fair number of queries in SSMS so I tend to be very careful.

If you can't find them on disk, there is a way to recover queries from the procedure cache as long as you catch them before they get pushed out of the cache. See also Execution Plan Caching and Reuse.

Answer 2

I have fixed this problem in SSMSBoost add-in that I develop:

http://www.ssmsboost.com/Features/ssms-add-in-sql-editor-contents-history

Autorecovery information is saved for all - saved and unsaved docs. You can even navigate through editor contents history and track executed queries.