visual-studiossisssms

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

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).

Solution

  • I'm using SSMS, aka Microsoft SQL Server Management Studio. I'm working late. Many SQL files are open. Windows runs updates, and suddenly reboots the computer without asking me. My jaw drops. My heart pounds. My head explodes. Panic sets in. When Windows and SSMS was restarted, it re-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, such as 2018, 2019, etc.

    OK, and now for the long version...

    How to search elsewhere for other versions of SSMS:

    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:

    enter image description here

    Good luck.