Restore of transaction log files

Question

question

Winkler asked May 27, '20

Restore of transaction log files

I'm working on a job with an arrangement where we are first supplied with a database backup file and then each day we receive the latest transaction for the previous 24 hours.

E.G.:
Wentworth_Live_20200515.bak
Wentworth _20200515230001.trn
Wentworth _20200516230001.trn
Wentworth _20200517230001.trn
Wentworth _20200518230001.trn
Wentworth _20200519230001.trn

We have restored the .bak to our server using SSMS and now want to automate daily replaying of the latest transaction log to the live database using a script via SQL Agent.

The following command works when I use SSMS to initially restore the .bak and then use script to restore a log file:

Restore Log Wentworth_Live from Disk='C:\Wentworth\Wentworth_20200517230001.trn' With NORECOVERY

After restore of .trn is complete the command ‘Restore Database Wentworth_Live With Recovery’ is applied to make the database accessible.

However, when I next try to restore a log file – e.g. the second in sequence – using same command I receive an error message:

Restore Log Wentworth_Live from Disk='C:\Wentworth\Wentworth_20200516230001.trn' With NORECOVERY

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

Obviously I’m missing a step (and particular point of understanding) and would appreciate any assistance on offer.

Many thanks

Stephen

restore transaction-log

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-05-28T07:32:05Z

anthony.green answered May 28, '20

You will need to restore the BAK along with all the TRN's in the sequence to get to the point you need.

EG to get to the 17th trn you would

Restore

Wentworth_Live_20200515.bak with no recovery
Wentworth _20200515230001.trn with no recovery
Wentworth _20200516230001.trn with no recovery
Wentworth _20200517230001.trn with recovery

To get to the 19th you would

Restore

Wentworth_Live_20200515.bak with no recovery
Wentworth _20200515230001.trn with no recovery
Wentworth _20200516230001.trn with no recovery
Wentworth _20200517230001.trn with no recovery
Wentworth _20200518230001.trn with no recovery
Wentworth _20200519230001.trn with recovery.

For each new TRN you get you will need to slot it in and restore in the order needed.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2020-05-28T08:13:25Z

Kev Riley answered May 28, '20

I agree with @anthony.green answer in that you will need to restore from the original BAK file each time, but another option if you only need to read from the database is to restore using a STANDBY file. This will bring the database online in a read-only mode, and allows for further log restores.

So instead of

Restore Database Wentworth_Live With Recovery

use

Restore Database Wentworth_Live With STANDBY='insert some path and name here'

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2021-08-19T09:42:44Z

SmithSean answered Aug 19, '21

You receive this error message because you haven’t restored SQL Server full backup or it has been restored without the “WITH NORECOVERY” option. All you need to do, before restoring a differential backup, is to make sure that a full database backup was restored with “WITH NORECOVERY” option.

Here you can find a simple example how to restore a differential backup

RESTORE DATABASE AdventureWorks FROM DISK = 'D:\AdventureWorks_Full.bak' WITH NORECOVERY

RESTORE DATABASE AdventureWorks FROM DISK = 'D:\AdventureWorks_Diff.bak' WITH RECOVERY

You can read this blog: https://www.stellarinfo.com/blog/ms-sql-restore-with-recovery-and-norecovery/

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question