SQL Server 2005 Management Studio - Recover Accidentally Closed Tab

Question

Is there a way to do this if an unsaved tab gets accidentally closed?

Answer 1

I was able to recover a query I was working on after accidentally closing the tab. If you actually ran the query, it should be in SQL Server's query cache. Query the query cache and order the results by creation date. More info on the SQL Server query cache:

Modify a query like this one (found at http://msdn.microsoft.com/en-us/library/ee343986(v=SQL.100).aspx)

SELECT cp.objtype AS PlanType,
       OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
       cp.refcounts AS ReferenceCounts,
       cp.usecounts AS UseCounts,
       st.text AS SQLBatch,
       qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;

to get your desired result. The "st.text" column will have the query that was run on the database server.

Answer 2

I also found at MSDN website that it is not possible to recover these files, but I would give a try to this (it worked for me):

1. Take a look in the folder C:\Users\YOURUSERIDHERE\Documents\SQL Server Management Studio\Backup Files\Solution1 and choose files for date when machine restarted or crash happened. SQLBlog.com

2. Take a look in the folder C:\Users\”[your username goes here]“\AppData\Local\Temp\ (this wasn't work for me because my .sql files had 0KB and .tmp files had something, but I couldn't find the way to 'extract' code from these .tmp files). Suppose that sometimes can be helpful, depending on reason of system reboot/crash. ayesamson.com