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:

ALTER DATABASE DatabaseName SET EMERGENCY;

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:

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

Then reset the database to Multi-User:

ALTER DATABASE DatabaseName SET 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.


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *