AutoRecover in SSMS

 

Periodically there is a crash, power surge, or sudden reboot of your computer.  Of course, you had SQL Server Management Studio (SSMS) open and you were working on something important.  That seems to be the only time there is a crash/reboot.  You can lose work that that was open in SSMS but has not saved.  There is an AutoRecover feature in SSMS so all may not be lost.  The AutoRecover default settings in SSMS are:

‘Save AutoRecover information every 5 minutes’

‘Keep AutoRecover information for 7 days’

With the default settings, you can lose up to 5 minutes of work on a sudden reboot.  The recovered information will be kept for 7 days so there is some time to get your files.  These settings can be modified, changing how often auto recover happens and how long the information is retained.  It depends on the criticality of things that you work on as to how often you would want AutoRecover to save your scripts.  This feature can be turned off but it is not recommended.  To change the settings, go to:

Tools -> Options -> Environment -> AutoRecover

 

When opening SSMS after that unexpected shutdown, SSMS tries to recover the unsaved queries and prompts a dialog box to the user.  You have the choice to recover files at that time.  If you skip over that step or the dialog box does not appear on re-open, the AutoRecovered files can be found on the server.  Navigate to the folder where the backup files are located and recover files
from there.

 

The settings in SSMS will determine what is available to recover and when it was last saved.  You don’t want AutoRecover to constantly run, but it needs to be set to a time that is appropriate for you and your job.