Have you ever face the SQL Server Management Studio crashes issue without saving the SQL scripts. In this case, SQL SERVER Auto Recovery Scripts is one of the helpful features introduced in SQL Server 2012. It automatically saves open SQL scripts according to user definable period of time. By default, this time period is set for 5 minutes. User can change it according to their needs. By default, this feature is turned on with default settings in SQL SERVER 2012 (SSMS).
Location of Auto Recovery Option in SSMS:
This option is available in Tools >> Options >> Environment >> AutoRecover
Settings available in Auto Recovery:
Given below are the settings available in Auto Recovery.
- Save AutoRecover Information every: By this setting, you can set automatic saving of your script; it means after how many minutes your script should be saved automatically. By default, this setting is after 5 minutes.
- Keep AutoRecover information for : By this setting, you can set for how many days SSMS should keep those files. By default, this setting is 7 days.
Here is full overview picture of Auto Recovery setting:
You can find the recovery files here: C:\Users\windows user\Documents\SQL Server Management Studio\Backup Files\Solution1
NOTE: SSMS window might get hang for some time due to “Saving Auto Recovery information“.
If you want to disable this feature then you can also do this by using two methods:
Open SSMS 2012 & click on Tools >> Options
Uncheck "Save AutoRecover information every" checkbox to disable Auto Recovery:
You can also do it by make adjustment in registry setting.
1. Open Registry
Start -> Run -> RegEdit
2. Find and modify registry value
The location of this registry key in SSMS 2012:
HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\General\AutoRecover
When you double click on “AutoRecover Enabled" then its value will be "1" (ON), it means that Auto Recovery setting is enable. To disable Auto Recovery setting, set it to "0" (OFF) then press OK to save.
So you can easily recover your unsaved SQL script from SQL Server Management Studio by using SQL Server 2012 AutoRecovery Feature.