Recover unsaved SQL query scripts

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?

Posting this in case if somebody stumbles into same problem.

Googled for Retrieve unsaved Scripts and found a solution.

Run the following select script. It provides a list of scripts and its time of execution in the last 24 hours. This will be helpful to retrieve the scripts, if we close our query window in SQL Server management studio without saving the script. It works for all executed scripts not only a view or procedure.

Use <database>
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

You may be able to find them in one of these locations (depending on the version of Windows you are using).

Windows XP

C:\Documents and Settings\YourUsername\My Documents\SQL Server Management Studio\Backup Files\

Windows Vista/7/10

%USERPROFILE%\Documents\SQL Server Management Studio\Backup Files

OR

%USERPROFILE%\AppData\Local\Temp

Googled from this source and this source.

A bit late to the party, but none of the previously mentioned locations worked for me - for some reason the back up/autorecovery files were saved under VS15 folder on my PC (this is for SQL Server 2016 Management Studio)

C:\Users\YOURUSERNAME\Documents\Visual Studio [version]\Backup Files\Solution1

ex:

C:\Users\YOURUSERNAME\Documents\Visual Studio 2015\Backup Files\Solution1

You might want to check your Tools-Options-Environment-Import and Export Settings, the location of the settings files could point you to your back up folder - I would never have looked under the VS15 folder for this.

Use the following location where you can find all ~AutoRecover.~vs*.sql (autorecovery files):

C:\Users\<YourUserName>\Documents\SQL Server Management Studio\Backup Files\Solution1

Maybe you don't find the suggested directories or your recovery file is missing, thanks god I replicated the crash with an unsaved script and lead me to this directory:

C:\Users\user\OneDrive\Documents\Visual Studio 2015\Backup Files\Solution1

So, maybe this saves your day :)