How to Recover Unsaved SQL Queries in SSMS (SQL Server Management Studio)

Summary: Lost a SQL script? Learn how to recover unsaved queries in SSMS using AutoRecover files, temporary system folders, and T-SQL script execution history.

How to Recover Unsaved Work in SQL Server Management Studio (SSMS)

Losing unsaved work can be incredibly frustrating, especially after spending hours tuning a complex script in SQL Server Management Studio (SSMS). Fortunately, SSMS has built-in safeguards. Here’s how you can find and recover your lost SQL files.

1. Default Location for Unsaved SQL Files

If SSMS crashes, it often saves temporary copies of your open queries. Depending on your version of Windows, check these directories:

• Windows Vista / 7 / 10 / 11:

%USERPROFILE%\Documents\SQL Server Management Studio\Backup Files
OR
%USERPROFILE%\AppData\Local\Temp

• Windows XP:

C:\Documents and Settings\<YourUsername>\My Documents\SQL Server Management Studio\Backup Files

2. View Recently Executed SQL Queries via T-SQL

If you executed the script before losing it, the code might still be in the SQL Server Plan Cache. Run the following query to retrieve recently executed scripts:

USE <Database_Name>;
GO

SELECT 
    execquery.last_execution_time AS [Date_Time],
    execsql.text AS [Script_Content]
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;
        

Note: Replace <Database_Name> with the database you were targeting.

3. How to Enable AutoRecover in SSMS

To prevent future data loss, ensure AutoRecover is properly configured. This feature automatically saves your scripts at set intervals.

1. Open SSMS.
2. Go to Tools > Options.
3. Navigate to Environment > AutoRecover.
4. Set "Save AutoRecover information every" to 5 minutes (or your preferred interval).

4. What Happens on Restart?

When SSMS restarts after an unexpected shutdown, it will typically present a "Recover Unsaved Queries" dialog. Always review this list before closing the window to ensure your work is restored.

Conclusion

By keeping AutoRecover enabled and knowing how to query the sys.dm_exec_query_stats DMV, you can significantly reduce the risk of losing critical T-SQL scripts. Always remember to save your work frequently (Ctrl+S) as a best practice!

Found this SQL tip helpful? Share it with your team or subscribe for more SQL Server administration guides!

How to Search SQL Server Error Logs Using T-SQL and Temp Tables

Summary: Learn how to efficiently query the SQL Server Error Log using sp_readerrorlog and temporary tables to filter out noise and identify critical system errors.

Read Specific SQL Server Errors Using a Temp Table

Ever need to quickly check recent errors in your SQL Server logs without scrolling through thousands of lines in the Log File Viewer? This handy T-SQL snippet allows you to query the error log and gather information about various processes and messages. It's a great way to get a snapshot of what's been happening in your environment.

Querying SQL Server Error Logs

The following SQL code creates a temporary table and populates it by reading the last seven SQL Server error logs (from 0 to 6). It then selects all entries where the process is not 'logon', helping you filter out routine login messages and focus on actual errors or system events.

-- Create a temp table to hold log data
CREATE TABLE #t (dt DATETIME, process VARCHAR(200), msg VARCHAR(1000));

-- Populate the table by reading current and archived logs
INSERT INTO #t EXEC sp_readerrorlog 0, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 1, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 2, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 3, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 4, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 5, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 6, 1, 'error';

-- Filter results to ignore routine logon messages
SELECT * 
FROM #t 
WHERE process <> 'logon'
ORDER BY dt DESC;

-- Cleanup
DROP TABLE #t;
        

---

Found this T-SQL tip helpful? Share it with your fellow DBAs or subscribe for more SQL Server troubleshooting guides!

How to Analyze SQL Server Execution Plans from Saved XML Files via T-SQL

Summary: Learn how to load saved SQL execution plan (.xml) files into SQL Server, query historical plan handles, and use XQuery to extract performance metrics like CPU and IO costs.

How to Analyze SQL Server Execution Plans from a Saved XML File

If you have an execution plan saved as an XML file (often with a .sqlplan extension), you aren't limited to just viewing it graphically. You can load it into SQL Server and query the XML directly to find specific performance bottlenecks. Here is how to read and analyze it using T-SQL.

1. Load the Execution Plan File

The OPENROWSET function is the most efficient way to pull an external .xml file into a SQL variable for processing.

DECLARE @xml XML;

-- Load file content into XML variable
SELECT @xml = CAST(FileData.BulkColumn AS XML)
FROM OPENROWSET(BULK 'C:\TEMP\MyPlan.XML', SINGLE_CLOB) AS FileData;

-- Verify the content
SELECT @xml AS LoadedPlan;
        

2. Retrieve Historical Queries via DMVs

If you don't have the file but need to find a plan for a recently executed query, use the Dynamic Management Views (DMVs). This snippet searches for a specific text pattern in your plan cache.

SELECT TOP 100 
    execquery.last_execution_time AS [Date_Time],
    execsql.TEXT AS [Script],
    execquery.plan_handle
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
WHERE execsql.TEXT LIKE '%WITH Temp_Accounts (%'
ORDER BY execquery.last_execution_time DESC;
        

3. Deep-Dive Analysis using XQuery

Once your plan is in an XML variable, use XQuery and namespaces to extract specific attributes like PhysicalOp, EstimateCPU, and EstimatedTotalSubtreeCost for every operator in the plan.

-- Define the namespace for SQL Server Showplans
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    c.value('.[1]/@PhysicalOp', 'nvarchar(max)') AS PhysicalOp,
    c.value('.[1]/@LogicalOp', 'nvarchar(max)') AS LogicalOp,
    c.value('.[1]/@TableCardinality', 'nvarchar(max)') AS TableCardinality,
    c.value('.[1]/@EstimatedTotalSubtreeCost', 'float') AS SubtreeCost,
    c.value('.[1]/@EstimateRows', 'float') AS EstimatedRows,
    c.value('.[1]/@EstimateCPU', 'float') AS EstimateCPU,
    c.query('.') AS NodeXml
FROM @xml.nodes('//RelOp') AS T(c)
ORDER BY SubtreeCost DESC;
        

💡 Final Thoughts

By leveraging SQL Server's XQuery capabilities and DMVs, you can gain deep insights into query performance behavior directly from saved files. This method is essential for automated performance auditing or comparing plans across different server environments.

Found this optimization guide helpful? Share it with your SQL community or subscribe for more advanced performance tuning tips!