While working with SQL Server Management Studio (SSMS), we can have crash issues. This causes loss of our scripts / queries / query windows data.
Sometimes we get “Not responding” error message and restarted again after automatically closed. We can’t stop this.
This will be frustrating to re-write all statements again.
So, there are some options to which we can follow and retrieve the unsaved scripts:
Make sure you are using the latest version of SSMS on your system. If you have older version, then might be all features will not available which can recover your un-saved scripts. You may need to recreate all then scripts from scratch or get from on any backups or version control systems which you are using.
Method 1 -Auto Recover:
SSMS has a feature called “Auto Recover”. To avoid losing unsaved scripts in the future, you can enable the Auto Recover feature in SSMS. You can do this by going to “Tools” > “Options” > “Environment” > “Auto Recover” and configuring the auto-recovery settings.
How it works: Open SQL Server Management Studio (SSMS) on your system. If you had unsaved scripts in SSMS that you accidentally closed or if the application crashed, you may be able to recover some of your work. When you open SSMS, you should see the “Connect to Server” dialog. If you don’t see it, you can open it from the “File” menu by selecting “Connect Object Explorer.” or an icon in the left pane. In the “Connect to Server” dialog, you can provide your server credentials, if you don’t have any, you can find a “Browse for more” button, which allows you to browse for and connect to your SQL Server instance in your network or on a system. Click it and connect to your SQL Server instance by providing all details. Once you are connected to your SQL Server, you can click the “New Query” button in SSMS to open a new query window. If SSMS detects any unsaved scripts from your previous session, it should prompt you to recover them. You may see a message like “Auto Recover information has been found for this document.” Click on the message to recover the unsaved script. Please don’t forgot to click YES, else you will loose all unsaved scripts. SSMS will display the recovered script in a new query window. You can now save this script or continue working on it.
If “Auto Recover” feature was not enabled on your system then you will not see any recovery prompt.
Method 2 -By QUERY:
SSMS also saved then last run scripts in system database which required for some features. There are few tables which keep historical data about what user done at the application. So there from we can run the query and get the details which we can use. Query as follows:
SELECT txt.TEXT AS [SQL Statement]
, qs.LAST_EXECUTION_TIME AS [Last Time Executed]
FROM SYS.DM_EXEC_QUERY_STATS AS [qs]
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(qs.SQL_HANDLE) AS txt
ORDER BY qs.LAST_EXECUTION_TIME DESC;
Method 3 – Recovery Folder:
Explore the Recovery Folder: SSMS stores temporary files in a specific recovery folder. It depends on your version of SSMS and your operating system. Common locations include:
For SSMS 17.x: C:\Users\\Documents\SQL Server Management Studio\Backup Files
For SSMS 18.x: C:\Users\\AppData\Local\Microsoft\SQL Server Management Studio\\Recovery
Copy these sql files if they are presents in recovery folder to query window and save them.