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

Warning: count(): Parameter must be an array or an object that implements Countable in /home/fbsqlcom/public_html/youdidwhatwithtsql.com/wp-content/plugins/wp-codebox/main.php on line 31

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.

SELECT TOP 1 @script = 'ALTER DATABASE [' + [name] + '] SET EMERGENCY;' + CHAR(13)
		+ 'ALTER DATABASE [' + [name] + '] SET SINGLE_USER;' + CHAR(13)
		+ 'ALTER DATABASE [' + [name] + '] SET MULTI_USER;' + CHAR(13)
FROM sys.databases
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