How to recover missing SQL auto-recover files in SSMS (Microsoft SQL Server Management Studio) after unexpected shutdown

Question

My SQL Server Management Studio (SSMS) closed unexpectedly. Many SQL files were open. When SSMS was restarted, it did reopen some auto-recover files, but not all of them, especially one in particular that I was working on, and potentially HOURS worth of work are missing.

Can the missing files be recovered?

This is a self-answered how-to question. See below.

Note: This is for SSMS, but may also work for SSIS (Microsoft SQL Server Integration Services).

Answer 1

I'm using SSMS, aka Microsoft SQL Server Management Studio. Working late. Many SQL files open. Windows ran updates, rebooted the computer. When SSMS was restarted, it opened some files, but not all of them, especially one in particular which I'd been working on for hours.

This answer is based on another one for Visual Studio: https://stackoverflow.com/a/898058/2705042

This is primarily for SSMS, but may also work for SSIS (Microsoft SQL Server Integration Services).

Short answer (because you're probably panicking right now, as I was):

Look in the following folder. The SSMS auto-recover files are there. Sort the files by date, with newest on top. Then check them all. The SQL file I was looking for was a few items down:

C:\Users\[yourusername]\Documents\Visual Studio 2017\Backup Files\Solution1

Keep in mind your files may be on a drive other than C:, like a USB flash drive, or a network.

If you have a different version of SSMS, the 2017 may be different.

OK, and now for the long version...

How to search elsewhere for other versions of SSMS:

• Visual Studio is used as a shell for SSMS (and SSIS).
• Depending on the version of SSMS, the Visual Studio version may be 2015, 2017, 2019, etc.
• Visual Studio has a built-in auto-backup system.
• The auto-save interval is set to 5 minutes by default.
• But it doesn't work correctly.
• If VS/SSMS/SSIS closes unexpectedly, when restarted it may NOT reopen all backup files.

Here's how to find them (maybe). Open a Command Prompt window, and type the following:

c:
cd\
dir "Backup Files" /s /b /ad

Wait for it to finish. You should get results like the following:

C:\>dir "Backup Files" /s /b /ad
C:\Users\{yourusername}\Documents\Visual Studio 2017\Backup Files
C:\>_

SSMS stores its backups (always?) in the folder Solution1, so based on the path that dir found in the above example, the full path might be:

C:\Users\{yourusername}\Documents\Visual Studio 2017\Backup Files\Solution1

Open a File Explorer window to the location that dir found. As mentioned above, sort the files by date, with newest on top. There may be many files, especially if you use the same version of Visual Studio for programming C#/VB.

If your SSMS queries all have the standard extension *.sql, those are the files to check. The one I was looking for was a couple items from the top. Below is an example from my system:

Good luck.