Corrupt WSS 3 Content Database Recovery

I had the pleasure of working with a client recently on an issue that was described to me only as "there was a problem with the Internet and then SharePoint stopped working." With such an in depth explanation to the problem I knew exactly where to start, the logs!

After looking over SharePoint's logs and the event logs, I was seeing errors dealing with the Network Service account not having access to the database and database does not exist type errors. This gave an indication that it could be a corrupt database.

Now the setup here for this client is a personal server running Windows 2003 R2 SP2 with WSS 3 SP1 and using Windows Internal Database SP2.

The first thing I did was look for the backups. Unfortunately the person that did the install of SharePoint did not configure the backups to run. I went to my second option and asked the IT guy in charge of the network if he had backups scheduled on the server. No luck there either.

The next thing I did was run through all of the Windows Updates that hadn't been applied and pushed those out to the server. I did choose to not apply WSS SP2 since I was already dealing with a database issue so I didn't need that update attempting to alter the database and making things worse.

Once all of the updates were run I downloaded the SQL Express Management Studio so that I could take a look at the databases on the box. Sure enough once I connected I was able to see the Content database marked as Suspect. At this point I had to put the database in emergency mode so that it would allow me to attempt to recover anything I could. Here is a script that will help you with the recovery process. You need to make sure you don't run the entire script at once because you may need to alter it after you run DBCC CHECKDB the first time. Once you run the check it will tell you what your options are for recovery based on it's analysis. Be sure to read the analysis file carefully so you don't run a higher level of recovery than is required.

-- Turn on Update
exec sp_configure 'update','1'
reconfigure with override
GO

-- Set database to Emergency so you can access data
ALTER DATABASE WSS_Content
SET EMERGENCY
GO

-- Set to Single User so you can perform maintenance
ALTER DATABASE WSS_Content
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

-- Run CHECKDB. Check log when complete for possible solutions
DBCC CHECKDB (WSS_Content)

-- Run suggested method to repair database
DBCC CHECKDB (WSS_Content, REPAIR_ALLOW_DATA_LOSS)

-- Set Database ONLINE
ALTER DATABASE WSS_Content
SET ONLINE
GO

-- Set to Multi User so everyone can access
ALTER DATABASE WSS_Content
SET MULTI_USER
WITH ROLLBACK IMMEDIATE;
GO

-- Turn off Update
exec sp_configure 'update','0'
reconfigure with override
GO

After executing the script I finally had SharePoint back up with minimal data loss and the client was happy! She had all of her documents and images uploaded to the server and would have lost a year's worth of work.

To wrap everything up I created a batch file that will backup the database and scheduled it to run weekly. I also recommended to the IT guy to configure the server to have a backup at least once a week. The client was already under the assumption that this was being done and was shocked when I told her that there were no updates. Hopefully that conversation wasn't too bad for the IT guy!

Lessons learned from this are definitely check all of your SharePoint environments, especially ones you didn't set up in the first place, and make sure there is a backup being performed. At minimum you should have a batch file set to run weekly. Even better would be a full database backup using a SQL Server maintenance plan if your environment is configured to use the full version of SQL Server. Also a full server backup will catch the file system so you will have a backup of the 12 hive. These are standard practices in large corporations, but in consulting you sometimes end up working with small clients that don't have an IT staff so you need to take responsibility and make sure all of this is done. It will make your life easier just in case something does happen!

Comments

fabian williams said…
This is a very good post! Awesome notes and explanation
January 5, 2010 at 10:29 AM
Post a Comment

Popular posts from this blog

SharePoint SP2 - Beware! (Update)

Here is some more fun stuff to know about installing SP2 on your SharePoint server. Microsoft just posted some info on the SharePoint team blog ( http://blogs.msdn.com/sharepoint/archive/2009/05/21/attention-important-information-on-service-pack-2.aspx ) that talks about how after installing SP2 your SharePoint license key will expire and your product will revert back to evaluation mode. Everything should still function normally, but after 180 days it will stop working unless you fix the problem. To do this you just need to reenter your product key in Central Admin. MS is working on a hotfix that should be out soon. Hopefully they push out a new install for SP2 as well so no one else has to go through this.
Read more

Thumbnail View of Slides

One thing you lose when you create a data view of a slide library is the ability to just show a thumbnail without some custom work. It's not difficult, just a pain. Here's the code so you don't have to waste time thinking about something that should be built in. Note: This works for ppt and pptx files. You'll have to think to make the rest work! <td class="ms-vb"> <xsl:variable name="fileExt" select="concat('_pp',substring-after(@FileLeafRef, '.pp'))" /> <xsl:variable name="fileName" select="substring-before(@FileLeafRef, '.pp')" /> <img border="0" src="/{@FileDirRef}/_t/{$fileName}{$fileExt}.jpg" /> </td>
Read more

Breaking Permissions and Web Part Access

Here is a tip about how to prevent your site owner or other users who have access to add web pages from getting an access denied message when they try to edit a page. Background You have a site collection with a top level site that you don't want anyone to access so you remove the permissions from it. Then on your sub-sites you break inheritance so you can get the security set up the way you want. The Issue When users go to edit a page to do something like add a web part they are given an access denied error. You can see this even if you have "Full Control" access on the sub-site. The Resolution Go to the top level site and create a new Group (ex: WebPartViewers), but don't give it any access. Next, add the users or groups that you want to be able to add and remove web parts from a page. You could add "Authenticated Users", but that is probably not a good idea since you will be opening up the web part library with this change. Now go to the Web Part Gall...
Read more