Auto-recovery feature and how to recover unsaved

Disaster recovery is one of the critical things and every IT or technology system faced with. Unfortunately, many times even our own work can be in danger.

Once in a while, everyone facing an unfortunate situation where losing time or even all work. When we coding, tweaking a query, or investigate issues, we do not want to lose even single minutes of work. Unexpected reboot, electricity malfunction, or SSMS crash, can ruin a whole day in fact.

Luckily, Microsoft developers have prepared for a disaster and built a recovery feature. We can find it right in SQL Server Management Studio.

• Tools > Options > Environment > AutoRecover

As we can see, this feature is simple and includes only two settings:

1. Save AutoRecover information every - Saving interval in minutes.

The SSMS will save an opened queries windows as files on a specific location.

2. Keep AutoRecover information for - The actual number of days to preserve auto-saved files, before deleting.

The AutoRecover feature is enabled by default with the above settings. In case of an unexpected shutdown, upon restart of SSMS, it will present a dialog window to the user, proposing to recover the unsaved queries.

As an alternative way, we can find scripts on the default location:

On Window XP -

C:\Documents and Settings\%USERNAME%\My Documents\SQL Server Management Studio\Backup Files\

On Window Vista, 7, 8, 10 -

C:\Users\%USERNAME%\Documents\Visual Studio ...(version year)... \Backup Files\

An additional place to check:

C:\Users\%USERNAME%\AppData\Local\Temp

There are unfortunate situations when something went wrong and scripts are not saved. We still have a chance!

If the query is not only "sitting" in the editor window and executed before the SSMS crash. We can cross fingers and try to use the following TSQL code:

USE [your DB];
GO

SELECT
	s.last_execution_time	AS [ExecTime], 
	t.text					AS [Statement] 
FROM
	sys.dm_exec_query_stats AS s
	CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
ORDER BY
	s.last_execution_time DESC;
GO

This is can be helpful only if you executed the query and the query is still in SQL Server’s memory. Means server was not rebooted and the query is not pushed out from the memory by other operations.

Important notes:

1. Auto recovery works for the files that already saved only. If we do something in the new session, and SSMS has crashed before saving interval is pas, we will lose our work.

2. Be careful when increasing the frequency of the savings, this may affect the time it takes for SSMS to start.

3. AutoRecover is an SSMS feature and not an engine one.

4. If you use any other tools (not SSMS), you cant enjoy it. Sorry :)

Conclusion: CTRL+S is your best friend, save your work frequently!

---

This post was originally published at https://www.linkedin.com/pulse/ssms-auto-recovery-feature-vitaly-bruk/