SQL Server Error 3159

Error number

3159

Severity

16 (user-correctable error)

State

1

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Managed Instance

Category

Backup & Restore

Last reviewed

23 October 2026

What it means

Error 3159 means you are restoring over a database whose transaction log holds changes that are in no backup yet, and SQL Server stops so that you do not lose them by accident.

In the FULL and BULK_LOGGED recovery models, every change since the last log backup exists only in the live transaction log, the tail of the log. Restoring over the database would throw that log away. So before RESTORE overwrites an existing database in those recovery models, SQL Server checks whether the tail has been backed up; if not, it raises 3159 and the statement ends with Msg 3013. Nothing has been changed yet.

The message offers two ways forward, and they are very different. BACKUP LOG ... WITH NORECOVERY saves the tail and leaves the database in the RESTORING state, ready for the restore sequence, so you lose nothing. WITH REPLACE (or STOPAT) tells SQL Server to overwrite the log, so every change after your last log backup is gone. Microsoft's own guidance is that REPLACE should be used rarely and only after careful consideration.

Common causes

• You are restoring a full backup over the same database to go back in time, and changes were made after the last log backup.
• A refresh or recovery script restores over a FULL recovery database without a tail-log backup step and without REPLACE.
• The restore dialog in SSMS was used with the tail-log backup option cleared (or a script was generated from it without that step).
• Recovery after a failure: the database is still online enough to have an un-backed-up log, and the tail has not been captured before the restore.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT d.name, d.recovery_model_desc, d.state_desc, rs.last_log_backup_lsn
FROM sys.databases AS d
JOIN sys.database_recovery_status AS rs ON rs.database_id = d.database_id
WHERE d.name = N'AppDb';

SELECT TOP (10) bs.type, bs.backup_start_date, bs.backup_finish_date,
       bs.first_lsn, bs.last_lsn, mf.physical_device_name
FROM msdb.dbo.backupset AS bs
JOIN msdb.dbo.backupmediafamily AS mf ON mf.media_set_id = bs.media_set_id
WHERE bs.database_name = N'AppDb'
ORDER BY bs.backup_finish_date DESC;

SELECT session_id, login_name, host_name, program_name, status
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(N'AppDb');

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

USE master;
BACKUP LOG AppDb
TO DISK = N'D:\Backups\AppDb_tail_20261023.trn'
WITH NORECOVERY;

RESTORE DATABASE AppDb
FROM DISK = N'D:\Backups\AppDb_full.bak'
WITH NORECOVERY;

-- ... RESTORE LOG for each log backup, in order, WITH NORECOVERY ...

RESTORE LOG AppDb
FROM DISK = N'D:\Backups\AppDb_tail_20261023.trn'
WITH RECOVERY;

BACKUP LOG AppDb
TO DISK = N'D:\Backups\AppDb_tail_20261023.trn'
WITH NO_TRUNCATE, CONTINUE_AFTER_ERROR;

RESTORE DATABASE AppDb
FROM DISK = N'D:\Backups\AppDb_full.bak'
WITH REPLACE;

Examples

Restoring over a FULL recovery database with recent changes

Throwaway database; the full backup goes to the instance default backup folder.

-- Throwaway database in FULL recovery, with a full backup
ALTER DATABASE AppDb SET RECOVERY FULL;
DECLARE @bak nvarchar(260) = CAST(SERVERPROPERTY('InstanceDefaultBackupPath') AS nvarchar(200)) + N'\cws_errlab_3159.bak';
BACKUP DATABASE AppDb TO DISK = @bak WITH INIT;

CREATE TABLE dbo.Orders (id int PRIMARY KEY);   -- work after the full backup
INSERT dbo.Orders VALUES (1);

USE master;
RESTORE DATABASE AppDb FROM DISK = @bak;        -- no tail-log backup, no REPLACE

Msg 3159, Level 16, State 1, Line 10
The tail of the log for the database "AppDb" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 10
RESTORE DATABASE is terminating abnormally.

RESTORE WITH NORECOVERY does not skip the check

Same throwaway setup. NORECOVERY on the RESTORE only leaves the result ready for more backups; it does not save the tail.

ALTER DATABASE AppDb SET RECOVERY FULL;
DECLARE @bak nvarchar(260) = CAST(SERVERPROPERTY('InstanceDefaultBackupPath') AS nvarchar(200)) + N'\cws_errlab_3159.bak';
BACKUP DATABASE AppDb TO DISK = @bak WITH INIT;
CREATE TABLE dbo.Orders (id int PRIMARY KEY);
INSERT dbo.Orders VALUES (1);

USE master;
RESTORE DATABASE AppDb FROM DISK = @bak WITH NORECOVERY;

Msg 3159, Level 16, State 1, Line 8
The tail of the log for the database "AppDb" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 8
RESTORE DATABASE is terminating abnormally.

How to prevent it

Make the tail-log backup the first step of every restore-in-place runbook for FULL recovery databases, and keep that file with the rest of the chain. In the SSMS restore dialog, leave the option to take a tail-log backup before restore selected.

Keep refresh scripts for test copies separate from recovery scripts for production, so that REPLACE only ever appears where losing the current data is intended.

Common questions

Learn more on CodeWithSQL

• SQL transactions and ACID explained

Official documentation

• MSSQLSERVER_3159 (Microsoft Learn)
• Tail-log backups (SQL Server)
• Restore a database to a point in time (full recovery model)

Last technically reviewed 23 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.