Introduction
During our productive hours, sometimes due to SSMS crashes or sudden machine reboots due to a windows update, during this time there is a possibility to lose the unsaved works / Script files. There is an option to recover our unsaved SQL queries in SSMS.
How to recover unsaved SQL Queries
By enabling the Auto-Recover, we can recover our unsaved SQL Scripts. All we have to do is, navigate to 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 AutoRecover 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.
Once the above settings is enabled in our SSMS, SSMS tried to recover the unsaved queries and prompts a dialog box to the user, in the case of an unexpected SSMS shutdown or if we navigate to the folder location below where the backup files are located.
- Open Local Disk (C):
- Open users Folder
- Find the folder relevant for your username and open it
- Click the Documents file
- 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.
In my case, this the folder path, C:\Users\user\Documents\Visual Studio 2017\Backup Files\Solution1
Conclusion
In this tips and tricks article, we have discussed about recover the unsaved SQL Queries in SSMS. I assume this tips was very useful. Lets discuss some other tips in upcoming article and if you have any feedbacks, please do share it in the comment section.
Consider reading other SQL articles of Mine
- Multiple Backup Files of the SQL Server database with SSMS and T-SQL
- Difference between CURRENT_TIMESTAMP vs GETDATE() vs SYSDATETIME() vs GETUTCDATE() in SQL Server
- Change Schema Name of Table in MS SQL Server
- Identifying object dependencies in SQL Server using SP_DEPENDS
- Select Names Starting With Vowels in MS SQL Server
- Display Line Numbers in a SQL Server Management Studio Query Window (SSMS)