Recover unsaved SQL query scripts

Question

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

Solution 1

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

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.

Solution 3

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

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

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

1. Open Local Disk (C):
2. Open users Folder
3. Find the folder relevant for your username and open it
4. Click the Documents folder
5. Click the Visual Studio folder or click Backup Files Folder if visible
6. Click the Backup Files Folder
7. Open Solution1 Folder
8. Any recovered temporary files will be here. The files will end with vs followed by a number such as vs9E61
9. 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

View more solutions

Comments

[Graeme]

I agree with John in principle; however, this happens from time to time to all of us: "I thought I saved that!" Make it a habit to SAVE FIRST before you start coding. Ctrl+S is your friend!

[Vasily]

Try SSMSBOOST (free community mode), or Redgate (not free, but very usefull)

[Rob Nicholson]

Unfortunately SSMS currently does not have the Undo Closed Tab feature. I have created the following Connect Item so Microsoft will hopefully add this in a future version: connect.microsoft.com/SQLServer/Feedback/Details/1044403

[spacebread]

Notepad++ has been spoiling me :(

[BumbleBee]

The Windows version i am working on is Windows 7. No files in this location. It's empty. under AppData\Local\Temp the Microsoft SQL Server query files are o kb.

[Matt]

As I mentioned, there's only a chance that it's in one of those locations. It's also likely that the files aren't recoverable. We've all been there before: you just need to be more careful about consciously saving your work.

[Matt]

That's definitely an outside-the-box way of finding your queries, but it will only hold information as long as the query remains in the plan cache (or until SQL Server is restarted, whichever comes first). You should still get into the habit of saving your work.

[Kevin Pope]

This is great - saved me a couple hours of work after a system crash! Found the files in Backup Files\Solution1

[BornToCode]

It might worth noting that you must have VIEW SERVER STATE permission in order to run this query

[Matthew Lock]

You may also be able to get older ones back by Restoring Previous Versions in Windows Explorer too.

[ctorx]

If only I could up-vote this answer for the number of hours I saved not having to rewrite a lost query.

[adolf garlic]

"this folder is empty"

[adolf garlic]

for W7: only empty folders for the first location and files that havevn't been touched in a month in the latter location

[adolf garlic]

A corporate auto install restarted my machine in my absence - this would have force closed all apps. There were tabs open, some saved some not. I have both these options selected and there are no recovery options nor 'temp' documents in other locations stated by other responders

[Jeff Fol]

If you are running this query on a database that has many queries running against it I would recommend adding a filter condition is the where clause such as where execsql.text like '%Some part of your script to search by%'

[Jonathan]

This is a marvellous add-in, by the way. It has just saved me two hours of work that I had thought I had saved, but hadn't. (I have no affiliation with Andrei)

[Mark]

tried looking in temp folder .. files like ~vsA497.sql exist but size of each is 0kb, even after opening i get no line written in the file. In backup folder there is no file.

[Matt]

They only stay in there for a bit of time - I couldn't even tell you what that is for certain - but sometimes you can catch it quick enough before it's lost.

[Kiquenet]

OS Windows 8.1 ?

[Andrew Steitz]

Wish I could up vote more. SSMSBoost has "saved my bacon" more than once.

[Sava Glodic]

Thanks for the query, this saved me a considerable amount of time when I accidentally closed the wrong tab. Since there's plenty of junk executions (aka stuff you don't want), may I offer a small addition of filtering out excess stuff by dumping it all into a temp table and then reading that, filtered?

[interesting-name-here]

This worked for me when I found out my canceled queries that were seemingly never going to cancel (force quit application) were dumping their data to a file here. Cleared out my temp and viola! 30 gigs freed up (really big pulls and lots of joins).

[RobsionKarls]

this work perfect for me, i ve been developing that script the entire morning then i closed the window without saving it and started to cry.

[Amazigh.Ca]

Thank's a lot, my VM was restarted remotly and I lost all my SQL code, you saved me too much time :)

[Jeff Moden]

This is the one that I couldn't remember. I didn't limit it to the folder posted and found what I had lost to an SSMS death.

[Mandar]

Or you can try : C:\Users\YourUserName\My Documents\SQL Server Management Studio\Backup Files\Solution1

[irgnosis]

This is correct for SSMS 2017, I found my lost files in this VS15 folder instead

[Radderz]

Do check this folder out; mine was hiding here too.

[DMadden51]

I am working with a very recent Windows 10 and this still works. I am surprised that I have not found a Ctrl-Shift-T functionality for this in SSMS yet. Anyone know if this functionality is hidden or available as an add-on to SSMS?

[Catchops]

Dude...saved my BACON!!! I had a file I was working on and SQL Management studio crashed (memory issue with Red-Gate tools). Somehow the file save failed or removed my file. Used this query and received back the query script I had been working on!

[dmoney]

C:\Users\YourUserName\AppData\Local\Temp recovered exactly what I lost. File was lost 5 mins ago, Windows 10, SQL Server Management Studio 17.

[Vitox]

C:\Users\YourUsername\AppData\Local\Temp saved my life! Thanks!

[Brian MacKay]

Dude. Wow. I was just about to give up and you saved me... A lot... I mean a lot of work. I am doing a complicated transition and I had all the notes on how to move prod over in this file. Thank you so much.

[alexkovelsky]

C:\Users\YourUsername\AppData\Local\Temp - sort by modified date, some ~####.sql files will have 0 kb, but some will probably contain your recent queries.

[CashCow]

This DOES NOT WORK. It does not save the SQLQuery[number].sql which are the unsaved scripts. It only seems to save the named ones.

[Travis Acton]

Saved my day! Thank you.

[CapnShanty]

amazing, the remote machine randomly restarted and I'd spent several hours on an incredibly important/complex script for a client due tomorrow, I was boutta quit lmao, thanks fam

[Kate]

I have both options but nothing was recovered after SSMS failed.

[Andreea]

Thank you! I found them in This PC > Documents > Visual Studio 2015 > Backup Files > Solution 1 > <~AutoRecover_type_file.sql>

[pmbAustin]

Updating for SSMS18 and up... I found it uses this folder: C:\Users\<username>\Documents\Visual Studio 2017\Backup Files\Solution1

[csteele]

Thank you for this! I ended up using the built-in search on Windows 10 and narrowed it down to files modified today to find where mine were being stored at.

[ppel123]

Yes that helped, the above solutions didn't help but with Everything I found it. Thanks.

[Rohit]

Thanks Man!, you saved me quite some hours. Above solution works for Windows server 2012 as well.

[whobetter]

@CashCow I just used this and it saved named and unnamed scripts for me. In lieu of a name, it appears that a tilde, "vs", and part of a guid are used. One of mine was named ~AutoRecover.~vsD4CA.sql

[adolf garlic]

this appears to return a bunch of random looking system queries rather than anything I have run...what gives?

[adolf garlic]

Invalid object name 'sys.query_store_query_text'.

[thecoolmacdude]

For SSMS 2018, I found my lost files in Documents\Visual Studio 2017\Backup Files\Solution1.