Restore From LDF File Only: Why It Fails and What Works

A restore from LDF file only is not possible. A log file holds changes, and changes need a database to apply to.

Five Questions, One Answer

The same five questions reach me by email, and the answer is the same each time. The table has them with the short answer.

A third party tool that claims to regenerate the database from a log file cannot do it. At most, such a tool reads the log and shows some recent changes. It cannot give you back the data that was never in the log.

Attaching does not help either. CREATE DATABASE with the FOR ATTACH option needs the primary data file, which an LDF file is not. The reason is the same each time. The log is not a copy of the data. The rest of this post shows why a restore from LDF file only fails, and what does work.

What a Log Backup Holds

The demo database is a small bakery order table. The script takes a full backup, adds two orders, and takes a log backup. Then it adds one more order and takes a tail-log backup, which is the last backup of the log. NO_TRUNCATE makes that backup work even when the data files are damaged. The demo database is healthy, so the option only shows the form.

The backup files need a folder. Set @folder to an existing folder that the SQL Server service account can write to. Keep the backslash at the end. Each block that uses a backup file starts with the same line, so change it there too.

IF DB_ID(N'LogOnlyRestoreDemo') IS NULL CREATE DATABASE LogOnlyRestoreDemo;
GO
ALTER DATABASE LogOnlyRestoreDemo SET RECOVERY FULL;
GO
USE LogOnlyRestoreDemo;
GO
SET NOCOUNT ON;
DECLARE @folder nvarchar(260) = N'C:\SQLBackups\';
DECLARE @full nvarchar(300) = @folder + N'LogOnlyRestoreDemo_full.bak';
DECLARE @log1 nvarchar(300) = @folder + N'LogOnlyRestoreDemo_log1.trn';
DECLARE @tail nvarchar(300) = @folder + N'LogOnlyRestoreDemo_tail.trn';
DROP TABLE IF EXISTS dbo.BakeryOrders;
CREATE TABLE dbo.BakeryOrders (OrderID int IDENTITY(1,1) NOT NULL PRIMARY KEY, Item varchar(40) NOT NULL);
INSERT INTO dbo.BakeryOrders (Item) VALUES ('sourdough'), ('focaccia'), ('baguette');
BACKUP DATABASE LogOnlyRestoreDemo TO DISK = @full WITH INIT, FORMAT;
INSERT INTO dbo.BakeryOrders (Item) VALUES ('pretzel'), ('ciabatta');
BACKUP LOG LogOnlyRestoreDemo TO DISK = @log1 WITH INIT;
INSERT INTO dbo.BakeryOrders (Item) VALUES ('brioche');
BACKUP LOG LogOnlyRestoreDemo TO DISK = @tail WITH INIT, NO_TRUNCATE;

Compare the sizes. The full backup processed 496 data pages. The two log backups processed 3 and 2 log pages. Your page counts differ a little. The history in msdb shows the same picture.

SELECT s.type AS BackupType, CONVERT(decimal(9,2), s.backup_size / 1048576.0) AS SizeMB
FROM msdb.dbo.backupset AS s
WHERE s.database_name = N'LogOnlyRestoreDemo'
ORDER BY s.backup_set_id;

Type D is the full backup and type L is a log backup. The log backups are tiny because they record only what changed. They hold no copy of the table as it was. A change that adds a pretzel order has nothing to attach to. The database that existed before it is missing.

Try a Restore From the Log Alone

Now simulate the loss. The script drops the database, which also removes its data and log files. The three backup files stay, and nothing else is touched. Then it tries to restore from the tail backup, first as a log and then as a database.

USE master;
GO
ALTER DATABASE LogOnlyRestoreDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE LogOnlyRestoreDemo;
GO
DECLARE @folder nvarchar(260) = N'C:\SQLBackups\';
DECLARE @tail nvarchar(300) = @folder + N'LogOnlyRestoreDemo_tail.trn';
RESTORE LOG LogOnlyRestoreDemo FROM DISK = @tail WITH RECOVERY;
GO
DECLARE @folder nvarchar(260) = N'C:\SQLBackups\';
DECLARE @tail nvarchar(300) = @folder + N'LogOnlyRestoreDemo_tail.trn';
RESTORE DATABASE LogOnlyRestoreDemo FROM DISK = @tail WITH RECOVERY;

Msg 3118, Level 16, State 1, Line 3
The database "LogOnlyRestoreDemo" does not exist. RESTORE can only create a database when restoring either a full backup or a file backup of the primary file.
Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.

Both statements fail with the same message. It tells the whole story of a restore from LDF file only. The second one ends with RESTORE DATABASE is terminating abnormally. The message says it directly. SQL Server creates a database from a full backup or a file backup of the primary file. It creates one from nothing else.

What Works: Full, Log and Tail

A restore from a log works when it follows a full backup. Restore the full backup with NORECOVERY, then every log backup in order, and the tail last with RECOVERY. NORECOVERY leaves the database in the restoring state, so the next file can be applied. RECOVERY on the last file brings it online. A reader of the old post described the same order. The chain must have no gap, and the tail backup is what saves the last changes.

DECLARE @folder nvarchar(260) = N'C:\SQLBackups\';
DECLARE @full nvarchar(300) = @folder + N'LogOnlyRestoreDemo_full.bak';
DECLARE @log1 nvarchar(300) = @folder + N'LogOnlyRestoreDemo_log1.trn';
DECLARE @tail nvarchar(300) = @folder + N'LogOnlyRestoreDemo_tail.trn';
RESTORE DATABASE LogOnlyRestoreDemo FROM DISK = @full WITH NORECOVERY;
RESTORE LOG LogOnlyRestoreDemo FROM DISK = @log1 WITH NORECOVERY;
RESTORE LOG LogOnlyRestoreDemo FROM DISK = @tail WITH RECOVERY;
SELECT OrderID, Item FROM LogOnlyRestoreDemo.dbo.BakeryOrders ORDER BY OrderID;

All six orders are back, including brioche, which only the tail backup held. The database came from the full backup. The log backups only moved it forward. That is the whole role of a log.

Two other cases are worth a line. With the data file but no log file, FOR ATTACH_REBUILD_LOG can attach the database. It must have shut down cleanly. A single lost data file is restored from its own file backup, and the log backups bring it forward.

Does the Log Hold the Whole History?

You could argue that the log has every change since the database was created, so it should be enough. It does not. In the full recovery model, SQL Server reuses the log space after each log backup. The log holds the changes since the last log backup, not the history of the database. The older changes live in the log backups, and those still need the full backup under them. A restore from LDF file only would need the whole history, and the log does not keep it.

What to Remember

A restore from LDF file only is a dead end. Keep a full backup, the log backups after it and a tail-log backup when something breaks. Test the restore before you need it. For the steps of a restore, read Restore Database Backup Using T-SQL, Step by Step.

When you finish the demo, remove the database and its backup history. The three backup files stay in the folder that you chose. Delete the files LogOnlyRestoreDemo_full.bak, LogOnlyRestoreDemo_log1.trn and LogOnlyRestoreDemo_tail.trn there yourself. SQL Server never deletes them.

USE master;
GO
IF DB_ID(N'LogOnlyRestoreDemo') IS NOT NULL
BEGIN
    ALTER DATABASE LogOnlyRestoreDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE LogOnlyRestoreDemo;
END;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'LogOnlyRestoreDemo';

A log file is not a database, it is the diary of one.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.