Recover unsaved SQL query scripts

Sql Sql Server Tsql Sql Server-2012 Ssms

Sql Problem Overview

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

Sql Solutions

Solution 1 - Sql

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

Solution 2 - Sql

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

%USERPROFILE%\AppData\Local\Temp

Googled from this source and this source.

Solution 3 - Sql

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.

Solution 4 - Sql

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

Solution 5 - Sql

I know this is an old thread but for anyone looking to retrieve a script after ssms crashes do the following

Open Local Disk (C):
Open users Folder
Find the folder relevant for your username and open it
Click the Documents folder
Click the Visual Studio folder or click Backup Files Folder if visible
Click the Backup Files Folder
Open Solution1 Folder
Any recovered temporary files will be here. The files will end with vs followed by a number such as vs9E61
Open the files and check for your lost code. Hope that helps. Those exact steps have just worked for me. im using Sql server Express 2017

ex:

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

Solution 6 - Sql

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 name>\OneDrive\Documents\Visual Studio <version>\Backup Files\Solution1

So, maybe this saves your day :)

Solution 7 - Sql

For SSMS 18, I found the files at:

C:\Users\YourUserName\Documents\Visual Studio 2017\Backup Files\Solution1

For SSMS 17, It was used to be at:

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

Solution 8 - Sql

I am using Windows 8 and found the missing scripts in the path below:

C:\Users\YourUsername\Documents\SQL Server Management Studio\Backup Files

Solution 9 - Sql

Go to SSMS >> Tools >> Options >> Environment >> AutoRecover

There are two different settings:

Save AutoRecover Information Every Minutes

This option will save the SQL Query file at certain interval. Set this option to minimum value possible to avoid loss. If you have set this value to 5, in the worst possible case, you can lose last 5 minutes of the work.

Keep AutoRecover Information for Days

This option will preserve the AutoRecovery information for specified days. Though, I suggest in case of accident open SQL Server Management Studio right away and recover your file. Do not procrastinate this important task for future dates.

Solution 10 - Sql

SSMSBoost add-in (currently free)

keeps track on all executed statements (saves them do disk)
regulary saves snapshot of SQL Editor contents. You keep history of the modifications of your script. Sometimes "the best" version is not the last and you want to restore the intermediate state.
keeps track of opened tabs and allows to restore them after restart. Unsaved tabs are also restored.

+tons of other features. (I am the developer of the add-in)

Solution 11 - Sql

You can find files here, when you closed SSMS window accidentally

C:\Windows\System32\SQL Server Management Studio\Backup Files\Solution1

Solution 12 - Sql

I was able to recover my files from the following location:

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

There should be different recovery files per tab. I'd say look for the files for the date you lost them.

Solution 13 - Sql

I use the free file searching program Everything, search for *.sql files across my C: drive, and then sort by Last Modified, and then browse by the date I think it was probably last executed.

It usually brings up loads of autorecovery files from a variety of locations. And you don't have to worry where the latest version of SSMS/VS is saving the backup files this version.

Solution 14 - Sql

For SSMS 18 (specifically 18.6), I found my backup here C:\Windows\SysWOW64\Visual Studio 2017\Backup Files\Solution1.

Kudos to Matthew Lock for giving me the idea to just search across my whole machine!

Solution 15 - Sql

None of the options above have helped me recover a query I have executed on a remote machine 4 days ago whose script file wasn't saved and got lost due to a corporate restart.

I ended up recovering my query with the help of the system views sys.query_store_query and sys.query_store_query_text.

SELECT t.query_sql_text 
FROM sys.query_store_query_text t
INNER JOIN sys.query_store_query q 
	ON t.query_text_id = q.query_text_id
WHERE  q.last_execution_time between '2021-06-30' and '2021-07-03'

Solution 16 - Sql

If you still haven't found the recovery folder, you can try the following:

Open SSMS.
Write some query on it.
Wait for a while to ensure the file is silently saved.
Open the Task Manager and terminate the SSMS application.
Reopen SSMS. You should be prompted to open your unsaved work.
Open the proposed file and hover over the file tab to see its location.

Content Type	Original Author	Original Content on Stackoverflow
Question	BumbleBee	View Question on Stackoverflow
Solution 1 - Sql	BumbleBee	View Answer on Stackoverflow
Solution 2 - Sql	Matt	View Answer on Stackoverflow
Solution 3 - Sql	DonQ	View Answer on Stackoverflow
Solution 4 - Sql	Mandar	View Answer on Stackoverflow
Solution 5 - Sql	frustrationmultiplied	View Answer on Stackoverflow
Solution 6 - Sql	frapeti	View Answer on Stackoverflow
Solution 7 - Sql	Jatin Patel	View Answer on Stackoverflow
Solution 8 - Sql	M PRATAP	View Answer on Stackoverflow
Solution 9 - Sql	noobjet	View Answer on Stackoverflow
Solution 10 - Sql	Andrei Rantsevich	View Answer on Stackoverflow
Solution 11 - Sql	Naveen	View Answer on Stackoverflow
Solution 12 - Sql	NonProgrammer	View Answer on Stackoverflow
Solution 13 - Sql	Matthew Lock	View Answer on Stackoverflow
Solution 14 - Sql	csteele	View Answer on Stackoverflow
Solution 15 - Sql	RodWall	View Answer on Stackoverflow
Solution 16 - Sql	Mario Vázquez	View Answer on Stackoverflow