Every developer once in a while, facing an unfortunate situation where they have not yet saved the work and their SQL Server Management Studio crashes. Well, you can minimize the loss by optimizing auto recovery settings. In this video we can see how to set the auto recovery settings.
Go to SSMS >> Tools >> Options >> Environment >> AutoRecover
There are two different settings:
1) Save AutoRecover Information Every Minutes
This option will save the SQL Query file at certain interval. Set this option to minimum value possible to avoid loss. If you have set this value to 5, in the worst possible case, you can loose last 5 minutes of the work.
2) Keep AutoRecover Information for Days
This option will preserve the AutoRecovery information for specified days. Though, I suggest in case of accident open SQL Server Management Studio right away and recover your file. Do not procrastinate this important task for future dates.
Related Tips in SQL in Sixty Seconds:
- Manage Help Settings – CTRL + ALT + F1
- SSMS 2012 Reset Keyboard Shortcuts to Default
- A Cool Trick – Restoring the Default SQL Server Management Studio – SSMS
- Color Coding SQL Server Management Studio Status Bar – SQL in Sixty Seconds #023 – Video
- Clear Drop Down List of Recent Connection From SQL Server Management Studio
- SELECT TOP Shortcut in SQL Server Management Studio (SSMS)
What would you like to see in the next SQL in Sixty Seconds video?
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Thanks Pinal for sharing this piece of information.
You asked in video, has it happened to you, my response is “Yes it happened to me couple of times. I had to re-write all code again”.
This is a great feature. I wish they had this feature in SQL 2008/2005, but I see it is not available in SQL Server 2008, I guess Microsoft included this feature starting SQL Server 2012.
Very useful feature by Microsoft.
~ IM.
Hi,
This is great but it works only on SSMS 2012, on SSMS 2005+ I use SSMS Tools Pack from Mladen Prajdic http://www.ssmstoolspack.com it has a current windows history that show you all the changes done to the query and it as the ability to recover from crashes, and many more features.
Hi Pinal,
Thanks for this post, is there any way to do the same in SQL Server 2008?
It’s really very helpful for SQL professionals.
Hi,
We are Agree this is the gud feature but if the SSMS crashes where this Auot recover option saved the file..And how do we get back script
~~Venkat
But, this doesn’t work for SSMS 2012.
Unfortunately it only works for previously saved files and MS doesn’t care: https://docs.microsoft.com/en-us/collaborate/connect-redirect
It’s really useless post because recovery does not work on most versions of MS Management Studio for not saved files manually. But I’m saving the last ver. of important files, I do not need to recover it after crash. And files that you did not save manually always are lost.