TSQL: Restore a database without a log (ldf) file

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;

One Comment

  1. Steve says:

    I used to do this through SSMS regularly and on purpose by just copying prod mdf files over to test environments and attaching them. SQL server spits our a warning and creates a new ldf on the fly without issue.

Leave a Reply

Current ye@r *