Archive for April 24th, 2011

Recovering SQL Server databases without a log file

I use two drives in RAID0 for my database server at home. This is pretty bad, for sure. The RAID0 log array has failed twice in six years.

I probably should just drop the extra $80 to add a third drive and make RAID5, or buy a couple more and make RAID10. When the failure happens, I don’t lose any data; all the data could be reloaded, but not from a backup — from the original data source. That’s quite time consuming.

I’ve found that I can have SQL Server create a log file if the volume is lost, and that allows me to again mount the related database. This still might cause trouble; I might lose data, I might end up with uncommitted or (half-committed, really) transactions, and so on. Since the server is usually quiescent, it just doesn’t matter to me.

Since I’m often doing this, and since the script is a bit elusive, here’s a note to myself that explains what to do.

First, I need to put the database into EMERGENCY recovery mode:


Then, I’ll issue a CHECKDB statement to make sure the database is OK.

DBCC CHECKDB('database');

I can then issue an ALTER DATABASE command to rebuild the log:


ALTER DATABASE DatabaseName REBUILD LOG ON (NAME=Database_Log,FILENAME='F:\SQLLogs\DatabaseName_log.Ldf');


Then reset the database to Multi-User:


before doing another CHECKDB to make sure everything is OK.

This isn’t right, and isn’t something I’d do in production, but it is quite fine for my lame little test server.

Troubling Education Stats

A Right Denied is a slide deck from the Organization for Economic Cooperation and Development. It’s full of stats that paint a pretty bleak picture about education in the United States. Some of the slides explain that there are internal inequities, where students in certain areas (or of certain races!) get worse educations than others. There are other sides that point out how the countries’ students do terribly in mathematics and science education compared to their international counterparts.