0

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

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

Details, and 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 the full path might be:

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

Open a File Explorer window there. As mentioned above, sort the files by date, with newest on top. Check them all. The one I was looking for was a couple items from the top. Below is an EXAMPLE where I found the file I was looking for: enter image description here

Good luck.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.