Recover queries after a SQL Server Management Studio (SSMS) crash or PC restart

There could be some instances when you have typed in that 500+ line stored procedure or formatted a query to your liking but didn’t save all that to a file. If the SQL Server Management Studio (SSMS) crashes for any reason before you could save your work, usually, the next time you start SSMS, it shows a dialog box as below to ask you if you’d like to recover the queries.

SSMS Query Recover Dialog
SSMS Query Recover Dialog

But sometimes it may not ask for recovery and it might seem that all the work is lost. Well, most of them (if not all) can still be recovered because the recovery dialog shows where are the backup files located.

On Windows XP, SSMS saves auto recover copies of queries as you work, in the folder –

C:\Documents and Settings\<user name>\My Documents\SQL Server Management Studio\Backup Files\Solution1

On Windows 7, the queries can be recovered from –

C:\Users\<user name>\Documents\SQL Server Management Studio\Backup Files\Solution1

The names of the auto saved files are bit cryptic so you might need to open all those files to find the one you are interested in.

SSMS Query Auto Recover

Best practice –

  • – Save your work frequently. This advice is good not just for web designers but software developers too!
  • – Use a version control system

6 thoughts on “Recover queries after a SQL Server Management Studio (SSMS) crash or PC restart

  1. sagar Mar 25, 2013 / 3:52 AM

    Tell me what are the setting i need to do on my PC to show the pop up for recover file.

    • Aalam Rangi Mar 25, 2013 / 9:24 AM

      It is an automatic behavior of SSMS. There is no user-tweakable setting. Somehow SSMS is not consistent with the pop-up. Maybe it depends on the way the SSMS is closed. If you see a pop-up then that is great, if not then you know where to look for the files now.

  2. Mangesh May 16, 2013 / 2:15 AM

    Very informative!! Saved hours of my rework..:)

  3. events Nov 15, 2013 / 2:27 AM

    Hey, I think your blog might be having browser compatibility issues.

    When I look at your blog in Safari, it looks fine but when opening in Internet Explorer, it has some overlapping.
    I just wanted to give you a quick heads up! Other then that, excellent

  4. Pingback: SQL Server

Have a question or a suggestion?

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s