Recover Script from Recently Closed Tab in SSMS You Forgot to Save

By:   |   Updated: 2022-05-16   |   Comments   |   Related: > SQL Server Management Studio


Problem

If you work with SQL Server, it's highly likely you work with SQL Server Management Studio (SSMS). And if you use it with any frequency, it's also highly likely you've run into one of these annoying scenarios before.

  • Scenario 1: You're working on a SQL script in SSMS and SSMS or your PC crashes.
  • Scenario 2: You're working on a SQL script in SSMS, clicked 'x' to close the Query window, and mistakenly clicked on 'No' instead of 'Yes' at the 'Save changes to the following items?' prompt.

It may appear you've lost your work and need to start over. Is there a straightforward way to recover your script?

Solution

Yes, there is. SSMS and SQL Server provide a couple of ways to recover your work depending on how it was lost.

  • Scenario 1 Solution: Recovering from a Crash with Auto Recover - Start SSMS and choose to recover what you were working on
  • Scenario 2 Solution: Recover from Accidentally Closing Tab by Querying Dynamic Management Views (DMVs) – Query views then copy and paste the SQL to Another Query Window

Note: This tip was written using SSMS 18.11.1 and SQL Server 2019 Developer Edition running on Windows Server 2019.

Scenario 1 Solution: Recovering from Crash with Auto Recover

In the first scenario we are building this this query in the AdventuresWorksLT database to obtain a customer list. This is a simple example and would not be a catastrophe to lose but it still would be a time-consuming nuisance to have to re-type it.

SELECT [c].[LastName]
     , [c].[FirstName]
     , [a].[AddressLine1]
     , [a].[AddressLine2]
     , [a].[City]
     , [a].[StateProvince]
     , [a].[PostalCode]
FROM [SalesLT].[Customer]            [c]
    JOIN [SalesLT].[CustomerAddress] [ca]
        ON [c].[CustomerID] = [ca].[CustomerID]
    JOIN [SalesLT].[Address]         [a]
        ON [a].[AddressID] = [ca].[AddressID]
ORDER BY [c].[LastName];
Customer List Query

You're working away and suddenly either SSMS or your PC crashes due to an application error, lost power, intermittent hardware failure, or a stop error aka 'blue screen of death', etc. It doesn't really matter why. It happened and it may appear you've lost your work.

To simulate this problem, I've opened the Task Manager and did an 'End Task' on SSMS.

End Task

This one is easily solved. All you have to do is reopen SSMS and you're presented with the 'Microsoft SQL Server Management Studio Recovered Files' window. Simply clicking 'Recover Select Files' will open a temporarily saved copy of the Query window.

Recover Files

Here's the saved script including formatting.

Recovered File

To show how Auto Recover works we'll take a look at the temp file(s) SSMS writes the content of each Query tab to. These files can be in a few various places depending on your OS and SSMS versions. I found mine here at %HOMEDRIVE%\%HOMEPATH%\Documents\Visual Studio 2017\Backup Files\Solution1 with this little bit of PowerShell.

Get-ChildItem *AutoRecover*.sql -Recurse
Temp Files

Open the file in your text editor of choice. And here it is.

Opened Temp File

The caveat with this method is it lets you recover from a crash, but the temp file is deleted when the tab is closed gracefully. How can we recover if we closed out properly and can no longer use the Auto Recover from temp file method?

Scenario 2 Solution: Recover from Accidentally Closing Tab by Querying Dynamic Management Views (DMVs)

Rather than solving the problem on the SSMS side this scenario is solved on the SQL Server side by querying a couple of Dynamic Management Views (DMVs). When a query is run, it's logged in DMVs.

Before we continue, let's get a little background on when DMVs came into place and what they are. DMVs were first introduced in SQL Server 2005. A regular view is the result set of a stored query against database tables. A DMV is very similar. DMVs are the result set of a stored query against system activity. The data viewed from the DMV is only valid until the SQL Server is restarted and the stored queries start fresh again. There are no underlying tables from which to pull data.

Continuing on, we'll use the same query as we did in the previous example.

SELECT [c].[LastName]
     , [c].[FirstName]
     , [a].[AddressLine1]
     , [a].[AddressLine2]
     , [a].[City]
     , [a].[StateProvince]
     , [a].[PostalCode]
FROM [SalesLT].[Customer]            [c]
    JOIN [SalesLT].[CustomerAddress] [ca]
        ON [c].[CustomerID] = [ca].[CustomerID]
    JOIN [SalesLT].[Address]         [a]
        ON [a].[AddressID] = [ca].[AddressID]
ORDER BY [c].[LastName];

We've run the query. Then we click the 'x' to exit the window knowing we'll be prompted to save it. Oops, accidentally clicked 'No'. This is something we've all done. Don't worry, we're about to see an eraser to clean up the mistake.

Closed Without Saving

If we were to look under %HOMEDRIVE%\%HOMEPATH%\Documents\Visual Studio 2017\Backup Files\Solution1 again we see the directory is empty.

windows explorer

Not to worry, the query is retrievable in the SYS.DM_EXEC_STATS and SYS.DM_EXEC_SQL_TEXT DMVs with the following query. (Note, selecting from DMVs requires VIEW SERVER STATE permission.)

SELECT txt.TEXT               AS [SQL Statement]
     , qs.LAST_EXECUTION_TIME AS [Last Time Executed]
FROM SYS.DM_EXEC_QUERY_STATS  AS [qs]
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(qs.SQL_HANDLE) AS txt
ORDER BY qs.LAST_EXECUTION_TIME DESC;

We run the query and see our SQL in the SQL Statement column.

Query DMVs

Now it's a simple task to copy the field and paste it into another Query window. As you can see It will need to be reformatted manually or with a formatting tool. But the important thing to note is the query is not lost and you don't have to start over.

Copy and Paste Query

The caveats with this method are the query must have been run. If it's just sitting in the query window and has not been run then there is no record that it exists. It's also possible you may have to search through the list to find what you want as other queries are recorded to the point of pushing yours out due to space. It showed up here at the top of the list because we queried the DVMs immediately and nothing else is running on the SQL Server. Also, don't forget the view data is cleared if the SQL Server is recycled.

Conclusion

We've seen two ways of recovering our potentially lost work in SSMS that can save some time and aggravation. However, there is still no better way to solve a problem than to not have it all. Save your work and use Ctrl-S frequently.

Next Steps



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2022-05-16

Comments For This Article





download














get free sql tips
agree to terms