Last Friday night one of my clients’ websites went down. Yes, on a Friday night. Why do websites go down usually outside normal business hours? I don’t know why. A Murphy’s Law would definitely explain it saying that “whatever can go down, will go down on a Friday night“.
In case you have to deal with the same issue in the future, I want to share with you my experience and the steps that I followed to recover a corrupted Sitecore web database in the particular circumstances of not having direct access to the SQL server where the corrupted database was managed.
How the Sitecore Web database got corrupted
While I was troubleshooting this issue, I recognized that the Sitecore web database was corrupted, because I tried to switch database context in Sitecore while using the platform in Desktop mode and this operation was failing throwing an exception: “Object reference not set to an instance of an object”. When this exception is thrown, it means that an object expected to exist by the application was null instead. It was a good indication that the web database didn’t have any items anymore.
How did this happen? Well, it is easy to wipe out completely a web database, probably too easy! The root “sitecore” item in the Sitecore tree was configured to be not publishable.
A content author performed a full site republish and all items of the Sitecore tree got deleted from the web database, because Sitecore deletes from the publishing target database items and sub-items that are not publishable anymore.
I was able to easily fix the root cause of the issue in Sitecore, making the “sitecore” root item publishable again. Then I tried to publish the full site again, but the publishing command was not doing anything, stopping after processing only 6 items, all marked as skipped in the publishing process.
These 6 items were the children of the “sitecore” root item: Content, Layout, Media Library, Social, System and Templates items. The publishing process relies on the existence of a parent item to create an item in the publishing target database. In this case the parent “sitecore” root item didn’t exist anymore, so no publishing process could be performed successfully.
If I only had direct access to the SQL Server…
…Then it would have been so easy to recover the corrupted database! Unfortunately I didn’t have access to it and the hosting company was not available outside business hours to provide the needed service to restore the database using a database backup generated before the corrupting publishing event occurred. And, of course, this issue occurred on a Friday night. The website would have been down for an entire weekend, not for just a night.
Challenges bring new discoveries
I couldn’t access the SQL server managing the corrupted database, but the web application could! I thought I could write an utility page to execute SQL queries, but it would have required a big effort between implementation and testing, probably too much for a Friday night.
So I started to research for an alternative solution and luckily I found what I was looking for. Sitecore 8.1 introduced new admin tool pages that I was not aware of. One of these is the SQL Shell admin page, a powerful (and dangerous!) tool to execute any SQL query using one of the connection strings defined in the Sitecore website configuration. This is its path to access it:
/sitecore/admin/sqlshell.aspx
This page is a Non Secure page and it is disabled by default, as explained in the message rendered on the “Non Secure Page Disabled” page where the Sitecore admin authenticated user is redirected to:
The steps to recover the database
Once I found the right tool to query the web database, I executed the following steps to recover it:
- I queried the web database to be sure that no records existed in the following SQL tables: Blobs, Descendants, Items, Properties, SharedFields, UnversionedFields and VersionedFields. No items were found.
- I installed a vanilla Sitecore instance targeting the same Sitecore version used by the website on my laptop.
- Using Microsoft SQL Server Management Studio, I generated a SQL script of the data stored in the tables mentioned in step 1, using the web database of the vanilla Sitecore instance created in step 2. A data script can be generated executing the following steps:
- Right-click on the web database and select Tasks –> Generate Scripts… option.
- Select the specific tables (database objects) to script.
- Click on the “Advanced” button to set the “Types of data to script” option to “Data only” value, to include only data in the script.
- Save the generated script.
- I removed any “GO” SQL command in the generated data script using a text editor, since it was not recognized as valid syntax by the SQL Shell admin page.
- I browsed to the SQL Shell admin page and I executed the SQL data script previously generated. This process takes few minutes to finish. If successful, no message is rendered in the Output area on the page.
- I recycled the web application app pool, because Sitecore caches items when the web application starts and SQL script operations on a database don’t trigger any event to purge this cache.
- I accessed the Sitecore platform in Desktop mode and I verified that I could finally switch the database context to use the web database. The database was finally recovered!
- As final step to recover the published content of the website, I republished the entire Sitecore tree after verifying that the root cause of the corruption was fixed, ensuring that the “sitecore” root item was publishable.
The website was finally up and running again, after only few hours of downtime.
Conclusions
In this blog post I described how to recover a corrupted Sitecore web database in the particular circumstances of not having access to the SQL server managing the database, using the Sitecore SQL Shell admin tool page. Always remember to use this tool page with caution, because with admin power comes great responsibility!
If you are interested in discovering other available Sitecore admin tool pages, I recommend to read this great post written by Kamruz Jaman: Sitecore Admin Pages Cheat Sheet – New Tools.
Thank you for reading!
Eureka! 