Introduction
This is a guide for recovering the SQL Script files that are lost when SSMS crashed or system crashed or because of any other reason. Sometimes, user by mistake closes the SQL Script window and loses the script that is written. In this scenario, no physical file will be stored on hard drive.
Background
Case 1: SSMS Crashes or System Failure
Couple of times, I came across the situation where I encountered SSMS error or the sudden system crash or system error which resulted in losing the script files that I am writing and it was unsaved or never saved. In this situation, generally when you restart SSMS and it asks you for recovering the files. But many times, it does not ask to recover the lost files or the unsaved scripts and you end up losing your script thinking that there is no way to recover the lost files or the script that was written.
Case 2: Sometimes Users Close the Query Window Without Saving
At times, it happens that users close the Query Window by mistake without saving the script. In that case, no physical file would be saved on the Hard Disk likewise in the case 1. This would make you think that you have lost your files and you cannot recover the files at all. But there is a way to recover such files too.
This tip helps users to recover these files manually.
Using the Code
Case 1: SSMS Crashes or System Failure
As I mentioned earlier, in this scenario you might end up thinking that the files are lost and there is no way to recover the files.
This is not the case always, even if SSMS does not prompt for recovering the files, there are 100% chances that those files are still on your hard drive.
Before considering that you have lost your files, you should check the following paths:
C:\Users\[User]\Documents\SQL Server Management Studio\Backup Files\Solution1
or
C:\Users\[User]\AppData\Local\Temp
Where [User] is the name of the user you are currently logged in as.
You might find your files in this location.
Below is the example of the files:
Case 2: Sometimes Users close the Query Window Without Saving
In case of closing the SQL Query Window by mistake or without saving it, you can recover your script using the following script:
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
This will provide you the list of scripts ran during the last 24 hours. You may browse through the list of scripts available after executing this query and then can retrieve your scripts/files. This works for all the executed scripts on the server including Stored Procedures and Views or Functions. This will list every single SQL Statement that was executed in the last 24 hours.
Example
Points of Interest
Hopefully, this would be a very helpful tip for the beginners who are new to SSMS. Hope this helps in recovering your lost SQL Script for whatever reason.
