It happens to the best of us and this post is more of a reminder for myself the next time it happens to me than anything else. You are working in SQL Server Management Studio, you have a few tabs open (OK, it was 123 this time, but lets stay focused on the purpose of this blog post – You should see my Desktop and all the icons on top of other icons…), and then suddenly you get the dreaded SSMS has stopped responding/crashed window. Sure you have been saving the important things along the way, but there are plenty of tabs that were just working queries for analysis that don’t really need to be saved but you still need them. What do you do?
For a long time SQL Server Management Studio has been auto-saving scripts for you behind the scenes, and theoretically it should autorecover those scripts for you when it is launched again after the crash. However, I have experienced many situations where that didn’t happen. For a long time these backup files were located in the following path:
C:\Users\[User]\Documents\SQL Server Management Studio\Backup Files\[Solution]
However, during my recent crash of SSMS 18.5 I learned that this folder no longer exists… It turns out that the backup scripts are being saved still but to the following path:
C:\Users\[User]\Documents\Visual Studio 2017\Backup Files\[Solution]
Whew…. As a general practice, I copy the contents of that to a safe location before relaunching SSMS to make sure that anything that was auto-saved for me is safe and sound before SSMS has a chance to do anything. Call me paranoid, call me crazy, but it makes me feel safer when I open the application again and pray that it is going to work correctly and recover my unsaved work.
Want to save yourself this headache entirely? SSMSToolsPack (https://www.ssmstoolspack.com/) has offered the ability to have a flight data recorder of all of your scripts and execution history with the SQL History option. Oh sometimes I miss the days when I was a DBA and had just one environment that I worked in routinely that had all of my favorite tools and toys installed to protect me from myself. Live and learn (well I guess learning would imply that I wouldn’t keep having this repeat, so I will just live on and Google to find this post next time it happens)…
3 thoughts on “Recovering Scripts After SSMS Crash”
The SSMSToolspack is amazing and a must have addition whether you do it on occasion or all the time. Just the warnings built in for update and deletes without a where clause have saved me more than once, the history is a feature I never had to use and forgot about.
the crash was due to the diagrams? 🙂
Thank you so much! I found my files in the VS 2017 directory as mentioned above. You have saved me.