Reputation: 10779
How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?
Upvotes: 251
Views: 417290
Reputation: 1284
Great solutions! I found another one, found the lost version in Restore Previous Versions button when right-clicking the file in File Explorer.
Upvotes: 0
Reputation: 587
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.
Upvotes: 0
Reputation: 1161
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 :)
Upvotes: 21
Reputation: 53
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...
Upvotes: 0
Reputation: 777
If you still haven't found the recovery folder, you can try the following:
Upvotes: 0
Reputation: 159
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'
Upvotes: 1
Reputation: 479
I know this is an old thread but for anyone looking to retrieve a script after ssms crashes do the following
ex:
C:\Users\[YourUsername]\Documents\Visual Studio [version]\Backup Files\Solution1
Upvotes: 23
Reputation: 1273
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.
Upvotes: 123
Reputation: 193
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!
Upvotes: 4
Reputation: 13476
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.
Upvotes: 6
Reputation: 2104
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
Upvotes: 20
Reputation: 3112
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.
Upvotes: 198
Reputation: 490
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
Upvotes: 38
Reputation: 1387
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.
Upvotes: 4
Reputation: 160
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.
Upvotes: 7
Reputation: 2935
SSMSBoost add-in (currently free)
+tons of other features. (I am the developer of the add-in)
Upvotes: 6
Reputation: 71
I am using Windows 8 and found the missing scripts in the path below:
C:\Users\YourUsername\Documents\SQL Server Management Studio\Backup Files
Upvotes: 7
Reputation: 144
You can find files here, when you closed SSMS window accidentally
C:\Windows\System32\SQL Server Management Studio\Backup Files\Solution1
Upvotes: 4
Reputation: 10779
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
Upvotes: 505