How to find unsaved file location for SQL query in management studio

Losing unsaved work can be frustrating, especially in SQL Server Management Studio (SSMS). Here's how to recover your work.

Default Location for Unsaved SQL Files

SSMS may store unsaved queries in temporary locations:

• Windows XP:
  C:\Documents and Settings\<YourUsername>\My Documents\SQL Server Management Studio\Backup Files
• Windows Vista / 7 / 10 / 11:

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

Replace <YourUsername> with your actual Windows username.

View Recently Executed SQL Queries

Run this SQL to view recent activity:

USE <Database Name>;
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;

⚠️ Replace <Database Name> with the name of your working database.

Enable AutoRecover in SSMS

1. Open SSMS.
2. Go to Tools > Options.
3. Navigate to Environment > AutoRecover.

Adjust settings like time interval and number of backups to keep.

What Happens on Restart?

SSMS prompts to recover unsaved queries after a crash:

Conclusion

Enable AutoRecover and know where unsaved files go to prevent data loss in SSMS.