Click here to Skip to main content
11,718,289 members (86,488 online)
Click here to Skip to main content

Tagged as

Retrieve Unsaved/Lost SQL Script Files due to SSMS Crash or Accidental SSMS Termination

, 20 Mar 2014 CPOL 11.7K 7
Rate this:
Please Sign up or sign in to vote.
Recover SQL Script files that are lost due to SSMS Crash or whatever the other reasons

Introduction

This is a guide for recovering the SQL Script files that are lost when SSMS crashed or system crashed or because of any other reason. Sometimes, user by mistake closes the SQL Script window and loses the script that is written. In this scenario, no physical file will be stored on hard drive.

Background

Case 1: SSMS Crashes or System Failure

Couple of times, I came across the situation where I encountered SSMS error or the sudden system crash or system error which resulted in losing the script files that I am writing and it was unsaved or never saved. In this situation, generally when you restart SSMS and it asks you for recovering the files. But many times, it does not ask to recover the lost files or the unsaved scripts and you end up losing your script thinking that there is no way to recover the lost files or the script that was written.

Case 2: Sometimes Users Close the Query Window Without Saving

At times, it happens that users close the Query Window by mistake without saving the script. In that case, no physical file would be saved on the Hard Disk likewise in the case 1. This would make you think that you have lost your files and you cannot recover the files at all. But there is a way to recover such files too.

This tip helps users to recover these files manually.

Using the Code

Case 1: SSMS Crashes or System Failure

As I mentioned earlier, in this scenario you might end up thinking that the files are lost and there is no way to recover the files.

This is not the case always, even if SSMS does not prompt for recovering the files, there are 100% chances that those files are still on your hard drive.

Before considering that you have lost your files, you should check the following path:

C:\Users\[User]\Documents\SQL Server Management Studio\Backup Files\Solution1

Where [User] is the name of the user you are currently logged in as.

You might find your files in this location.

Below is the example of the files:

Case 2: Sometimes Users close the Query Window Without Saving

In case of closing the SQL Query Window by mistake or without saving it, you can recover your script using the following script:

Use <database> 
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery 
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC  

This will provide you the list of scripts ran during the last 24 hours. You may browse through the list of scripts available after executing this query and then can retrieve your scripts/files. This works for all the executed scripts on the server including Stored Procedures and Views or Functions. This will list every single SQL Statement that was executed in the last 24 hours.

Example

Points of Interest

Hopefully, this would be a very helpful tip for the beginners who are new to SSMS. Hope this helps in recovering your lost SQL Script for whatever reason.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Elisha Cornelius
Chief Technology Officer
India India
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionYOU SAVED MY LIFE. Fantastic tip!! Pin
David Grossman11-Dec-14 16:10
memberDavid Grossman11-Dec-14 16:10 
GeneralTrue and not true... Pin
Paw Jershauge20-Mar-14 3:18
professionalPaw Jershauge20-Mar-14 3:18 
GeneralRe: True and not true... Pin
Elisha Cornelius31-Mar-14 23:49
memberElisha Cornelius31-Mar-14 23:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150901.1 | Last Updated 20 Mar 2014
Article Copyright 2014 by Elisha Cornelius
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid