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 your 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 dialog 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 icon

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 option 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 pane, 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 icon

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 icon

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 on which you are currently working, of the next time you start SSMS, click the Save workspace button on the Tab navigation pane:

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:

 

March 21, 2016