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.
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.