Have you ever encountered a situation where SQL Server Management Studio (SSMS) crashes while trying to restore a database backup stored in Azure Blob Storage? If so, you’re not alone. In this blog post, we will explore a workaround to fix this issue and get your database restored without any further interruptions.
Recently, I was assisting a client who was facing a disaster situation and urgently needed to restore a database backup stored in Azure Blob Storage. However, every time they tried to use the “Restore Database” menu option in SSMS and selected the device as URL, the application crashed. This unexpected behavior was causing a lot of frustration and hindering their progress.
Upon further investigation, we discovered that the crash was occurring due to some issue with the information stored by SSMS about the storage account. To confirm this, I tested the same steps on my own SSMS and encountered the same crash. This led us to believe that the problem was not specific to the client’s environment but rather a general issue.
After some trial and error, we found a simple workaround that resolved the issue. We asked the client to connect to the Azure storage directly using SSMS by following these steps:
- Open SSMS
- Click on “Connect to Storage Account” option
- Enter the necessary details to connect to the storage account
Surprisingly, even when connecting to the storage account directly, we encountered the same crash. This confirmed our theory that the problem lies in the information stored by SSMS about the storage account.
To resolve this issue, we needed to clean up the information stored in the cache of SSMS. Unfortunately, we couldn’t find a way to clean up only the Azure storage-related information from the user profile. As a result, we had to delete the entire saved settings of SSMS by removing the “sqlstudio.bin” file from the user profile. Here’s how you can do it:
- Open the Run dialog by pressing Windows key + R
- Paste the following path and press Enter:
%AppData%\Microsoft\SQL Server Management Studio - Locate the folder corresponding to your SSMS version (e.g., 11.0 for SQL Server 2012, 12.0 for SQL Server 2014, etc.)
- Delete the “sqlstudio.bin” file in that folder (You can also rename the file and SSMS will create a new one)
It’s important to note that this solution is not the most elegant one, as it will delete all saved information in SSMS, including usernames, passwords, server names, and any other settings you may have customized. However, it is a necessary step to resolve the crash issue and get your database restored.
By following this workaround, my client was able to successfully restore the database backup from Azure Blob Storage without any further crashes. If you’re facing a similar issue, I hope this solution helps you as well.
Remember, it’s always a good practice to regularly backup your databases and test the restore process to ensure you’re prepared for any unforeseen situations. Stay proactive and keep your SQL Server environment running smoothly!