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];
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.
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.
Here’s the saved script including formatting.
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
Open the file in your text editor of choice. And here it is.
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.
If we were to look under %HOMEDRIVE%\%HOMEPATH%\Documents\Visual Studio 2017\Backup Files\Solution1 again we see the directory is empty.
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.
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.
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
- Look here for an extensive number of SSMS related tips: SQL Server Management Studio Tips
- And here you’ll find a great tip on DMVs: Dynamic Management Views
