Friday, October 16, 2009

A MS SQL Server 2008 Data Recovery Technique

I had some MS SQL Server 2008 database files that became corrupted recently. The files were detached from the SQL Server before they became corrupted. So when I tried to attach them, I get errors saying that the SQL server failed on some assertion at location "logmgr.cpp": 4217. The asserted expression was "lfh->lfh_startOffset == startOffset".

After many many different attempts, I finally got the files to attach. The steps that I took are things most people would normally try, but I added a twist to the first step:

1. Create a new blank database with the same name and schema: tables, constraint, etc. At this point you may have guessed what should follow. But before you go on, do the following: set it into emergency and single mode.

ALTER DATABASE CorruptedDB SET EMERGENCY
GO
ALTER DATABASE CorruptedDB SET SINGLE_USER;
GO

2. The rest of the steps are fairly typical. Stop the SQL Server service from the SQL Server Configuration Manager.
3. Replace the files for this blank database with the corrupted files.
4. Restart the SQL Server service from the SQL Server Configuration Manager.

This allowed me to access a set of database files that were otherwise no longer readable. Although I could not even look at the table definitions in the SQL Server Management Studio, I could selelct all the data rows out of it, and insert into a new database with the same schema:

USE
SET IDENTITY_INSERT ON
INSERT INTO .[dbo].
(
,...

SELECT
,...

FROM CorruptedDB.[dbo].
GO
SET IDENTITY_INSERT OFF
GO


Sunday, October 4, 2009

Microsoft CRM 4.0 IFD Requires SQL Server Report Service

This does not seem obvious, but if the Report Service of your SQL Server is not running, the users of Microsoft CRM 4.0 cannot login via the Internet, although intranet login would still work.

Apparently the Internet Facing Deployment (IFD) of Microsoft CRM 4.0 depends on the Report Service of Microsoft SQL Server.

Also in Microsoft CRM's "Deployment Manager", select "Servers" in the left pane, you will see a list of servers in the right pane. Make sure that the server for the "Full Server" roll is enabled. Otherwise, IFD would not work, either.