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!