sys.dm_exec_query_stats is very helpful dynamic management view when it comes to query recovery.
Sometimes we write queries to perform some operations in the database and after that close the script window without saving. Later on realize, if only had I saved it, it would have saved some time. Here the solution comes namely sys.dm_exec_query_stats.
This view can recover your query from sql server. Let me create an example to explain it.
First open a new query window and execute the given below script and close the query without saving it.
USE AdventureWorks2012 GO Select * from Sales.vStoreWithContacts Select * from Production.ScrapReason Select * from Sales.vStoreWithAddresses Select * from Purchasing.vVendorWithContacts Select * from HumanResources.Shift Select * from Purchasing.vVendorWithAddresses Select * from Production.ProductCategory Select * from Purchasing.ShipMethod Select * from Production.ProductCostHistory Select * from Production.ProductDescription Select * from Sales.ShoppingCartItem
After that, just execute the given below script, it will recover your query.
SELECT Distinct qt.TEXT AS [Queries] FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
The reason why we used distinct in the above query is, there are queries that we have executed multiple times that will appear as well.
Still, I am searching for a solution that can link these queries to the user who executed it so the recovery would be easier. Will update you on this in the upcoming post.
Reference : MSDN
SQL Server Portal 