9/14/14

Recover a SQL Server management studio query

We often come across the case when SSMS crashes and perhaps your query was not saved. If you have the AutoSave feature enabled, you can find a temporary file at this location:

c:\users\{username}\SQL Server Management Studio\Backup Files\Solution1

If you do not have this feature, you can still recover the script to the state when it was last executed by reading the information from the dynamic management views and functions. This can allow us to capture the SQL statements that were executed on the system for a particular period of time. Please note that this only works while the query statistic cache is not reset. 

To recover your query, use the following query and enter a search value and time frame.

USE MASTER 
GO

SELECT query.last_execution_time AS [Date Time], execsql.text AS [Script]
FROM sys.dm_exec_query_stats AS query
CROSS APPLY sys.dm_exec_sql_text(query.sql_handle) AS execsql
WHERE execsql.text like '%TAG-TO-SEARCH%' and last_execution_time > getdate()-1
ORDER BY query.last_execution_time DESC


In this query, I am only looking for queries for the last day. You can remove that date constraint to expand the search.  The TAG-TO-SEARCH value should be replaced by a word that can help you get a closer match.


You can find detail information of dynamic views here