I had to restore a bunch of databases missing a log file today. This was only a test server but I wanted to get it running as quickly as possible.
The script here is based on the advice in this blog post from sqlskills. Note that this is very dodgy and should only be used as a last resort (i.e. no backups) in a production environment.
This script here will generate the commands required to create a new log file. Execute the commands one-by-one to get the database online.
DECLARE @script VARCHAR(MAX); SELECT TOP 1 @script = 'ALTER DATABASE [' + [name] + '] SET EMERGENCY;' + CHAR(13) + 'ALTER DATABASE [' + [name] + '] SET SINGLE_USER;' + CHAR(13) + 'DBCC CHECKDB (N''' + [name] + ''',REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;' + CHAR(13) + 'ALTER DATABASE [' + [name] + '] SET MULTI_USER;' + CHAR(13) FROM sys.databases WHERE state_desc = 'RECOVERY_PENDING'; PRINT @script;