restore database from transaction log problem

I'm following instructions here to save database state and restore it from any state i previ开发者_Python百科ously saved. I cannot however get the RESTORE given in the example there working. I always get a message

This log file contains records logged before the designated mark. The database is being left in the Restoring state so you can apply another log file.

I think the problem is with the FILE version, but from what I see in the example, it should be N in DB and N-1 in Log, and that's how I always set it. Any help would be appreciated.

---

It sounds like you need to apply more transaction logs. You have to first restore the database with a full backup to some point in the past. You may then restore zero or more differential backups (in order) to skip forward as far as possible. Finally, you have to restore every transaction log that has log records beginning from the start of the most recently restored backup (full or differential) all the way up to the restore time in question.

RESTORE DATABASE Blah FROM DISK = 'blah 201105210000.bak' WITH NORECOVERY;
RESTORE LOG Blah_Log FROM DISK = 'blah 201105210100.trn' WITH NORECOVERY;
RESTORE LOG Blah_Log FROM DISK = 'blah 201105210200.trn' WITH NORECOVERY;
RESTORE LOG Blah_Log FROM DISK = 'blah 201105210300.trn' WITH NORECOVERY;
RESTORE LOG Blah_Log FROM DISK = 'blah 201105210400.trn' WITH NORECOVERY;
RESTORE LOG Blah_Log FROM DISK = 'blah 201105210500.trn' WITH STOPAT 'x', RECOVERY;

My syntax may not be perfect but the concept is solid, you have to restore all the transaction logs in order. If you pick a t-log that is too early you get a message about that, or if you pick one that is too late you'll get a different message. The message you posted indicates to me that the t-log you're trying to apply has log records entirely before the transaction id of the last restore applied. Find more t-logs from after that.

If t-logs shouldn't be missing but are, look in the SQL jobs or maintenance plans to see if you have two t-log backups going on different schedules. If this was happening, you must collect all the t-logs from both the backups as only all together can they be a proper and unbroken chain that will let you restore up to the point in time you want.

---

I couldn't get the example to work either unless I changed the last statement to the name of the transaction, rather than the description. (SQL Server Express 2017)

RESTORE LOG AdventureWorks2012
  FROM AdventureWorksBackups
    WITH FILE = 4,
    RECOVERY,
    STOPATMARK = 'ListPriceUpdate';

---

When the STOPAT argument is specified, SQL Server (2008 Express at least) appears to assume the NORECOVERY option even when RECOVERY is specified. I assume that's why it generates the message (not an actual error, correct?). To complete the operation, an additional step may be added to the process.

...
RESTORE LOG      Blah_Log FROM DISK = 'blah 201105210500.trn' WITH STOPAT 'x' WITH NORECOVERY;
RESTORE DATABASE Blah_Log WITH RECOVERY;