Retrieve unsaved SQL query scripts due to SQL Server Management Studio crash

Recovering work after SQL Server Management Studio crashes

Imagine a situation of working in SSMS, and suddenly the power goes out, the system gets crashed, SSMS gets crashed or the work session was abruptly terminated for any other reason, which causes the loss of unsaved work on various SQL scripts. In this situation SSMS provide prompt window for recovered data after restarting:

But unfortunately, many times SSMS, for whatever reason, doesn’t ask to recover the lost files or the unsaved scripts.

An alternative way to retrieve the data after SQL Server Management Studio crash is from the Solution1 folder which is located for

XP under:

C:\Documents and Settings\[User]\My Documents\SQL Server Management Studio\Backup Files\

or for Vista/Windows 7, 8, 10 under:

C:\Users\[User]\Documents\SQL Server Management Studio\Backup Files\

Quick tip: Auto recovery only works for the files that have been previously saved. So, files that have not been saved before (e.g. SQLQuery1.sql, SQLQuery2.sql etc.) after crashes SQL Server Management Studio will not be recovered

Retrieve unsaved SQL query scripts/files

There are situations when SQL Server Management Studio or a particular query window is accidently closed before saving scripts. To retrieve unsaved SQL query scripts, in this situation, use the following code:

Example is borrowed from: Retrieve Unsaved/Lost SQL Script Files due to SSMS Crash or Accidental SSMS Termination

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

This will provide the list of all executed scripts on the server that was executed in the last 24 hours.

Retrieve unsaved SQL query scripts/files with the Tab navigation option

Using the Tab navigation feature from ApexSQL Complete, a free add-in for SSMS and Visual Studio, all scripts can be retained in any of the aforementioned scenarios e.g. SSMS crash.

To enable the Tab navigation feature, go to the ApexSQL Complete menu, from the list choose the Options command and under the Tab navigation tab check the Enable tab navigation option:

To use this feature, from the ApexSQL Complete menu, choose the Tab navigation option to initiate the Tab navigation panel, which will show all opened tabs under the Open tab section:

All closed tabs will be placed under the Closed tab, regardless of whether they were previously saved or not:

Quick tip: Once enabled, the Tab navigation feature starts to monitoring opened or closed tabs no matter whether if the Tab navigation pane is initiated or not

Quick tip: All opened and closed tabs are stored by default in the Tab navigation folder located in C:\Users\<current_user>\AppData\Local\ApexSQL\ApexSQLComplete\TabNavigation, and can be copied and used on another machine where ApexSQL Complete is already installed

Recovering data after SQL Server Management Studio crashes with the Tab navigation option

To restore all opened tabs after an unexpected termination off a work session, the Restore previous session after crash option must be checked:

Additionally, to restore the queries that are currently in use, the next time when SSMS is started, click the Save workspace button in the Tab navigation panel:

To restore a previously saved workspace, click the Restore workspace option from the toolbar:

An option to automatically restore previously saved workspaces at the next start of SQL Server Management Studio is by checking the Restore tabs on startup from -> Saved workspace option:

Useful resources:

• Recover unsaved SQL queries from SQL Server Management Studio (SSMS)
• Auto Recover information is not saved
• Retrieve Unsaved/Lost SQL Script Files due to SSMS Crash or Accidental SSMS Termination