THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Recover Backed-up query files in SQL Server Management Studio

This is a useful little tip that I discovered today.

If SQL Server Management Studio (SSMS) ever crashes on you for whatever reason then there is a chance that you’ll lose whatever queries you had open at the time of the crash. Sometimes when you re-open SSMS it will ask you if you want to re-open any files that you recover but for some reason it doesn’t always do that and in that instance you may think that your work has gone for good.

Happily that’s not the case, not always anyway. Even if SSMS does not prompt you to recover those files they are probably still present on your machine. Go and check in your C:\Users\YOURUSERIDHERE\Documents\SQL Server Management Studio\Backup Files\Solution1 folder and you should find them waiting for you.

image

 

I’m sure most of you could have worked out where they were stored, the real trick is knowing that they are stored in the first place and now….you do!

@Jamiet

Published Monday, July 19, 2010 11:25 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Lee Everest said:

Sweet! So these were still there with no message asking if they could be recovered?  Nice tip.  I wonder how long they stay there :|

July 20, 2010 7:53 AM
 

Wiseman82 said:

The query history feature of SSMS Tools Pack can be quite useful for similar reasons:

http://www.ssmstoolspack.com/

The add-in also has a number of other useful features.

Still good to know about the built-in recovery feature and where the files are stored when it doesn't prompt you. :-)

July 20, 2010 2:49 PM
 

Pete said:

This just saved me a days work. I setup a really complex query and the power went off. I stupidly hadn't saved the query.

July 30, 2010 8:59 AM
 

ITLG said:

Thank you! thank you! thank you!!!!! I just recovered a whole day of work after as system crash :)

August 23, 2010 5:41 PM
 

Sharon said:

Wow! Thanks so much!!

December 3, 2010 9:49 AM
 

Kevin said:

Awesome! Now, is this documented in BOL?  I wasn't able to find it.

Thanks!

February 17, 2011 5:07 PM
 

ravindra kumar said:

thanks.you save my life:)

March 10, 2011 2:09 AM
 

Eric said:

You just saved my life

April 18, 2011 5:49 PM
 

Danny said:

Saved my day!!!

June 15, 2011 3:46 AM
 

Vikas said:

Thanks you saved half a day work for me. :)

June 19, 2011 8:53 PM
 

Chandan said:

I believe that it saves the solution files only if the management studio crashes or the operating system reboots due to some error or power failure. Only in these cases i have seen the management studio asking me if i wish to recover those files.

What if i close the management studio cleanly myself. will the files be still saved automatically?

PS: Will test it today for sure!

July 19, 2011 6:51 AM
 

Skip M. Cherniss said:

Great information. I will be sure to pass this on. Thank you.

August 1, 2011 10:19 AM
 

Robin Ryder said:

You are the BEST.  Saved me for a bad UPS.  Had a day's worth of work that I really didn't want to try to recreate!!!

August 23, 2011 8:51 AM
 

TERCON said:

Thanks for your great tip!

Saved a lot of work for me :)

October 27, 2011 1:40 AM
 

nidhi prasad said:

Either you can search the Backup Files and target for Solution1 Folder. This is how i got my lost SQL file. But thanks to show me the way

January 18, 2012 11:22 AM
 

John said:

You STAR!!!!

March 30, 2012 6:38 AM
 

Shyam said:

Thank you so much.. it saved my esteem!!!!

June 4, 2012 12:20 AM
 

Venkat said:

Awesome. I knew it would be saved somewhere, because sometimes when SQL Server crashes it offers to restore files. But sometimes it doesn't. And I used to resign to fate and not attempt to find where they were. This time I googled it and here I find this gem. Internet and Google make me lazy! Thanks for making me lazier :)

June 18, 2012 10:00 AM
 

JC said:

Works with XP as well (just need to get to the specific user folder). Thanks

June 18, 2012 7:31 PM
 

BMR said:

Thank you!!! Saved me hours...

July 5, 2012 8:07 AM
 

Manohar said:

Thanks for your great tip!

Saved a lot of work for me :)

July 18, 2012 6:14 AM
 

Sana said:

Many Many Thanks!!

October 10, 2012 2:15 PM
 

Vivek said:

Awesome, you saved me hell lot of time today

January 2, 2013 12:28 AM
 

Paul VH said:

Wow! Yeah, I probably could have figured it out but I never took the time.  When I was not prompted I figured they just didn't exist.  I'm not sure when I saved the query I was working on as it was a lot of tweeking...

September 20, 2013 4:26 PM
 

DVT said:

My folder Solution1 is empty after system crash, so unlucky

June 16, 2014 7:15 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement