Recover unsaved SQL query scripts

Question

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

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! — Graeme, Jul 12 '13 at 18:04
Try SSMSBOOST (free community mode), or Redgate (not free, but very usefull) — Vasily, Nov 27 '14 at 02:38
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: https://connect.microsoft.com/SQLServer/Feedback/Details/1044403 — Rob Nicholson, Mar 12 '15 at 07:00

BumbleBee · Accepted Answer · 2013-02-25T17:17:02.077

485

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

edited Feb 25 '13 at 17:17

answered Feb 22 '13 at 23:37

BumbleBee

10,429
20
78
123

27

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. – Matt Feb 23 '13 at 00:00
6

It might worth noting that you must have VIEW SERVER STATE permission in order to run this query – BornToCode Jan 16 '14 at 14:02
6

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%' – Jeff Fol Mar 30 '16 at 18:13
5

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? – Sava Glodic Sep 27 '16 at 08:57
5

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. – RobsionKarls Mar 21 '17 at 19:38
Thank's a lot, my VM was restarted remotly and I lost all my SQL code, you saved me too much time :) – Amazigh.Ca Apr 19 '17 at 14:25
2

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! – Catchops Feb 01 '19 at 20:53
this appears to return a bunch of random looking system queries rather than anything I have run...what gives? – adolf garlic Dec 07 '21 at 14:49

score 189 · Answer 2 · edited May 15 '19 at 21:19

189

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.

edited May 15 '19 at 21:19

b_levitt

7,059
2
41
56

answered Feb 22 '13 at 23:21

Matt

2,982
1
22
23

2

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. – BumbleBee Feb 22 '13 at 23:26
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 Feb 22 '13 at 23:46
2

This is great - saved me a couple hours of work after a system crash! Found the files in `Backup Files\Solution1` – Kevin Pope Nov 07 '13 at 17:12
You may also be able to get older ones back by Restoring Previous Versions in Windows Explorer too. – Matthew Lock May 29 '14 at 08:55
4

If only I could up-vote this answer for the number of hours I saved not having to rewrite a lost query. – ctorx Aug 04 '14 at 20:41
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 Feb 23 '16 at 08:25
2

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. – Mark Jun 16 '16 at 12:03
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. – Matt Jun 16 '16 at 13:19
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). – interesting-name-here Feb 10 '17 at 21:00
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? – DMadden51 Oct 25 '18 at 13:05
2

C:\Users\YourUserName\AppData\Local\Temp recovered exactly what I lost. File was lost 5 mins ago, Windows 10, SQL Server Management Studio 17. – dmoney Feb 05 '19 at 13:59
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. – alexkovelsky Apr 15 '19 at 13:37
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. – CashCow Jul 12 '19 at 10:41
5

Updating for SSMS18 and up... I found it uses this folder: C:\Users\\Documents\Visual Studio 2017\Backup Files\Solution1 – pmbAustin Oct 02 '20 at 16:06
@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` – whobetter Jul 23 '21 at 17:40

score 104 · Answer 3 · edited Jun 14 '21 at 13:34

104

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.

edited Jun 14 '21 at 13:34

Valeh Mikayilzadeh

813
7
18

answered Aug 01 '17 at 12:53

DonQ

1,083
1
7
8

11

This is correct for SSMS 2017, I found my lost files in this VS15 folder instead – irgnosis Dec 01 '17 at 18:00
1

Do check this folder out; mine was hiding here too. – Radderz Feb 19 '18 at 16:03
1

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. – Brian MacKay Feb 25 '19 at 21:57
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 – CapnShanty Mar 04 '20 at 23:26
Thank you! I found them in This PC > Documents > Visual Studio 2015 > Backup Files > Solution 1 > <~AutoRecover_type_file.sql> – Andreea Sep 07 '20 at 09:01
For SSMS 2018, I found my lost files in Documents\Visual Studio 2017\Backup Files\Solution1. – thecoolmacdude Mar 24 '22 at 13:40

score 37 · Answer 4 · edited Nov 01 '17 at 09:28

37

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

edited Nov 01 '17 at 09:28

Mohammad Anini

5,073
4
35
46

answered Aug 23 '13 at 08:57

Mandar

480
4
5

5

"this folder is empty" – adolf garlic Feb 23 '16 at 08:24
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. – Jeff Moden May 26 '17 at 16:28
1

Or you can try : C:\Users\YourUserName\My Documents\SQL Server Management Studio\Backup Files\Solution1 – Mandar May 29 '17 at 12:34

score 19 · Answer 5 · edited Nov 03 '22 at 18:46

19

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 :)

edited Nov 03 '22 at 18:46

jimhark

4,938
2
27
28

answered Nov 12 '16 at 00:46

frapeti

1,090
13
18

score 19 · Answer 6 · edited Jun 15 '21 at 04:31

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

score 17 · Answer 7 · answered Jul 17 '20 at 06:10

17

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

answered Jul 17 '20 at 06:10

Jatin Patel

2,066
11
13

1

Thanks Man!, you saved me quite some hours. Above solution works for Windows server 2012 as well. – Rohit Lal Jun 07 '21 at 15:26

score 7 · Answer 8 · edited Apr 30 '15 at 11:24

7

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

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

edited Apr 30 '15 at 11:24

eebbesen

5,070
8
48
70

answered Apr 30 '15 at 06:40

M PRATAP

71
1
1

score 7 · Answer 9 · edited Dec 31 '15 at 20:37

7

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

There are two different settings:

1) 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.

2) 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.

edited Dec 31 '15 at 20:37

Siyual

16,415
8
44
58

answered Dec 29 '15 at 06:21

noobjet

160
1
10

12

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 – adolf garlic Feb 23 '16 at 08:27
I have both options but nothing was recovered after SSMS failed. – Kate May 25 '20 at 20:01

score 6 · Answer 10 · answered Dec 29 '15 at 14:33

6

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)

answered Dec 29 '15 at 14:33

Andrei Rantsevich

2,879
20
25

1

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) – Jonathan May 20 '16 at 14:23
Wish I could up vote more. SSMSBoost has "saved my bacon" more than once. – Andrew Steitz Sep 08 '16 at 19:33

score 4 · Answer 11 · edited Aug 29 '13 at 10:28

4

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

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

edited Aug 29 '13 at 10:28

Kjuly

34,476
22
104
118

answered Aug 29 '13 at 10:11

Naveen

144
5

***OS Windows 8.1 ?*** – Kiquenet Aug 11 '16 at 11:52

score 4 · Answer 12 · edited Oct 19 '16 at 19:23

4

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.

edited Oct 19 '16 at 19:23

RamenChef

5,557
11
31
43

answered Mar 16 '15 at 19:53

NonProgrammer

1,337
2
23
53

score 3 · Answer 13 · answered Jul 22 '20 at 08:53

3

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.

answered Jul 22 '20 at 08:53

Matthew Lock

13,144
12
92
130

1

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. – csteele Oct 14 '20 at 19:46
1

Yes that helped, the above solutions didn't help but with Everything I found it. Thanks. – ppel123 Apr 26 '21 at 10:21

score 2 · Answer 14 · answered Oct 14 '20 at 19:45

2

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!

answered Oct 14 '20 at 19:45

csteele

173
2
6

score 0 · Answer 15 · answered Jul 06 '21 at 15:48

0

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'

answered Jul 06 '21 at 15:48

RodWall

149
1
9

Invalid object name 'sys.query_store_query_text'. – adolf garlic Dec 07 '21 at 14:55
@adolfgarlic I didn't mention it but this applies to SQL Server 2016 and later. – RodWall Feb 08 '23 at 09:13

score 0 · Answer 16 · answered Oct 01 '21 at 06:08

0

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.

answered Oct 01 '21 at 06:08

Mario Vázquez

717
10
9

This did not work for me. – Michael W. Mar 09 '23 at 14:43

score 0 · Answer 17 · answered Aug 02 '22 at 19:02

If you happen to have RedGate tools for SSMS, then they have a really useful tool called Tab History which will show you all of your open and closed tabs (even scripts which you wrote and closed without executing).

There should be a button on the Toolkit toolbar which looks like this which will open a window with a list of all scripts (which also has a search feature so you can search for your script):

I know that this won't be relevant for most people, but it might help someone out there who has lost their work...

score 0 · Answer 18 · answered May 24 '23 at 18:26

This is for the Apple / Mac Os computers:

I was using Azure Data Studio:

You should be able to retrieve the unsaved scripts on your local:
/Library/Application Support/azuredatastudio/Backups/<numbered folder>/untitled

The files will have a - prefix. For example: -635baea3 I had to review these folders and files and searched by date

you may open these files using vim:
vim ./-635baea3

Hope this is useful.

Recover unsaved SQL query scripts

18 Answers18

Linked