SQL Server Error 3117

Error number

3117

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

24 October 2026

What it means

Error 3117 means you tried to apply a log or differential backup to a database that has already been recovered (or was never restored), so it is no longer waiting for more backups.

A restore sequence has two phases. While backups are applied WITH NORECOVERY, the database stays in the RESTORING state and is "ready to roll forward": the next differential or log backup can be added. Recovery (WITH RECOVERY, which is the default when you specify neither) rolls back unfinished transactions and opens the database. From that moment it has a new history, and further log or differential backups from the old one cannot be applied. Trying anyway raises 3117, followed by Msg 3013 naming RESTORE LOG or RESTORE DATABASE.

So the message almost always means that an earlier step in your script recovered the database too soon: a RESTORE DATABASE without any recovery option, the SSMS restore dialog left on "RESTORE WITH RECOVERY", or a stray RESTORE DATABASE ... WITH RECOVERY. The failed statement changes nothing. To add the remaining backups you have to restart the sequence from the full backup, this time with NORECOVERY on every step except the last.

Common causes

• The full backup was restored without NORECOVERY. RESTORE defaults to WITH RECOVERY, so the database was opened straight away.
• The differential, or an earlier log backup, was restored WITH RECOVERY (or with no option) before the remaining log backups.
• In the SSMS restore dialog the recovery state was left at RESTORE WITH RECOVERY while restoring the files one at a time.
• A log shipping secondary or a standby copy was brought online (recovered), and the restore job then tries to apply the next log backup to it.

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 name, state_desc, is_in_standby, recovery_model_desc
FROM sys.databases
WHERE name = N'AppDb';

SELECT TOP (10) rh.restore_date, rh.restore_type, rh.recovery, rh.[replace],
       bs.backup_finish_date, mf.physical_device_name
FROM msdb.dbo.restorehistory AS rh
JOIN msdb.dbo.backupset AS bs ON bs.backup_set_id = rh.backup_set_id
JOIN msdb.dbo.backupmediafamily AS mf ON mf.media_set_id = bs.media_set_id
WHERE rh.destination_database_name = N'AppDb'
ORDER BY rh.restore_history_id DESC;

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;
RESTORE DATABASE AppDb
FROM DISK = N'D:\Backups\AppDb_full.bak'
WITH REPLACE, NORECOVERY;

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

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

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

RESTORE LOG AppDb
FROM DISK = N'D:\Backups\AppDb_log_1100.trn'
WITH STANDBY = N'D:\Backups\AppDb_undo.tuf';

Examples

Restoring a log backup after a full restore WITH RECOVERY (the default)

Throwaway database; the backups go to the instance default backup folder. The RESTORE DATABASE has no recovery option, so it recovers the database.

-- Throwaway database in FULL recovery: a full backup and a log backup
ALTER DATABASE AppDb SET RECOVERY FULL;
DECLARE @p nvarchar(200) = CAST(SERVERPROPERTY('InstanceDefaultBackupPath') AS nvarchar(200));
DECLARE @full nvarchar(260) = @p + N'\cws_errlab_3117.bak',
        @log  nvarchar(260) = @p + N'\cws_errlab_3117.trn';
BACKUP DATABASE AppDb TO DISK = @full WITH INIT;
CREATE TABLE dbo.Orders (id int PRIMARY KEY);
INSERT dbo.Orders VALUES (1);
BACKUP LOG AppDb TO DISK = @log WITH INIT;

USE master;
RESTORE DATABASE AppDb FROM DISK = @full WITH REPLACE;   -- recovers: RECOVERY is the default
RESTORE LOG AppDb FROM DISK = @log;

Msg 3117, Level 16, State 1, Line 13
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 13
RESTORE LOG is terminating abnormally.

Restoring a differential after the full backup was recovered

Same throwaway setup with a differential backup. The follow-up message names RESTORE DATABASE, because a differential is restored with RESTORE DATABASE.

ALTER DATABASE AppDb SET RECOVERY FULL;
DECLARE @p nvarchar(200) = CAST(SERVERPROPERTY('InstanceDefaultBackupPath') AS nvarchar(200));
DECLARE @full nvarchar(260) = @p + N'\cws_errlab_3117.bak',
        @diff nvarchar(260) = @p + N'\cws_errlab_3117_diff.bak';
BACKUP DATABASE AppDb TO DISK = @full WITH INIT;
CREATE TABLE dbo.Orders (id int PRIMARY KEY);
BACKUP DATABASE AppDb TO DISK = @diff WITH INIT, DIFFERENTIAL;

USE master;
RESTORE DATABASE AppDb FROM DISK = @full WITH REPLACE, RECOVERY;
RESTORE DATABASE AppDb FROM DISK = @diff;

Msg 3117, Level 16, State 1, Line 11
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 11
RESTORE DATABASE is terminating abnormally.

How to prevent it

Treat NORECOVERY as the normal option during a restore and RECOVERY as the deliberate last step. Generate restore scripts from msdb backup history (or your backup tool) so that the recovery option is set correctly for each file, and review the last line before running them.

Practise the full restore sequence on a test server, including a differential and several log backups. The first time you discover that the default is WITH RECOVERY should not be during an outage.

Common questions

Learn more on CodeWithSQL

• SQL transactions and ACID explained

Official documentation

• RESTORE statements: arguments (Transact-SQL)
• Restore and recovery overview (SQL Server)
• Apply transaction log backups (SQL Server)

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