Recover unsaved SQL query scripts

sql sql-server tsql sql-server-2012 ssms
BumbleBee picture BumbleBee · Feb 23, 2013 · Viewed 274.9k times · Source

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?

Answer

BumbleBee picture BumbleBee · Feb 23, 2013

Posting this in case if somebody stumbles into same problem.

Googled for Retrieve unsaved Scripts and found a solution.

Run the following select script. It provides a list of scripts and its time of execution in the last 24 hours. This will be helpful to retrieve the scripts, if we close our query window in SQL Server management studio without saving the script. It works for all executed scripts not only a view or procedure.

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

Related questions

Sequence vs identity

SQL Server 2012 introduced Sequence as a new feature, same as in Oracle and Postgres. Where sequences are preferred over identities? And why do we need sequences?

Read More
Scope of temporary tables in SQL Server

I wrote a stored procedure to import and transform data from one database to another. Each import would take a single company ID and import all data related to this company. To help with the transformation step I use temporary …

Read More
TSQL OVER clause: COUNT(*) OVER (ORDER BY a)

This is my code: USE [tempdb]; GO IF OBJECT_ID(N'dbo.t') IS NOT NULL BEGIN DROP TABLE dbo.t END GO CREATE TABLE dbo.t ( a NVARCHAR(8), b NVARCHAR(8) ); GO INSERT t VALUES ('a', 'b'); INSERT t VALUES ('a', …

Read More