0

From what I understand a transactional log file will store the transactions made after the last transactional log back up.

Lets say I have a database with 10 entries. I take a full back up. I insert one entry, take a log back up (log1.trn) I insert one entry, take another log back up (log2.trn)

Why does ssms not allow me to restore the database with the full back uo and log2.trn skipping log1.trn file?

Greatly appreciate any help thanks!

I have tried using T-sql to accomplish this as well but to no avail.

The error i receive is

Msg 4305, Level 16, State 1, Line 5 The log in this backup set begins at LSN 528000018207200001, which is too recent to apply to the database. An earlier log backup that includes LSN 525000023381600001 can be restored. Msg 3013, Level 16, State 1, Line 5 RESTORE LOG is terminating abnormally.

0

4 Answers 4

6

No.

Because you'd lose some of the transactions that happened on the server.

You always have to follow the correct Restore sequence in order to restore your database to a consistent state.

  1. Restore the full backup you want to start from
  2. (Optionally) Restore the latest differential backup
  3. Restore all the log files starting from that full/differential in correct sequence

You can find more information about this as well when looking for "Backup chain"

It's important to know that log backups contain all of the transactions from the latest transactional backup, and after a transaction log backup, the log file gets cleared.

An example would be:

  • You create a new table : dbo.PleaseDontLoseMe
  • Transaction log backup 1 taken here -> Log gets cleared --
  • INSERT INTO dbo.PleaseDontLoseMe
  • Transaction log backup 2 taken here -> Log gets cleared --

Now we restore the database and we skip Log backup 1, SQL would be trying to insert a record in a table that wasn't ever made.

0
2

Why does SSMS not allow me to restore the database with the full back up and log2.trn skipping log1.trn file?

Given such an over-simplified example, I can see why you would think this should be possible.

In reality, your database is doing loads of stuff for itself, all the time, and every change is logged as transactions and then there's all the myriad of changes it's taking from Users.
Recovering a database is about getting a "broken" database back into a working state - as it was at a given point in time, starting from a known, fixed point (the last full backup). That means that everything that it did (since that last backup) has to be done again, in exactly the same way as it was before. You cannot "miss bits out" because there's just no capability to do so.

If you could, somehow, "record" all the SQL that was "thrown at" the database, then you could "edit" that stream to remove any bits you didn't want and "replay" that. But that's nothing whatever to do with Recovering your database.

0

Changed records since last backup are flagged. When you backup - full, diff, or log, those flags are reset.

You can skip restoring log 1 if you have a differential backup after it to restore. Restore full, diff, log2.

Else you need to supply all changes since the full backup for data consistency (truth) to prevail. You'd corrupt your data without the safeguard of sequentially applying all transactions.

-1

I think log files are crucial to ensure data consistency through redo/undo operations. Directly skipping transaction log files during an SQL Server database restore is not possible.

You can try an alternative option to restore with Sufficient Space: If disk space limitations prevent restoring all logs, consider a temporary solution. Restore to a machine with enough space, then:

• Change the database to a less intensive logging mode • Shrink the transaction logs. • Create a new backup on this instance. • Use this new backup for restoration on the target machine. If the log size stems from the source server, see if the originating party can shrink the transaction log before sending the backup files. However, this might not be feasible for very active databases.

These methods change how the database stores information. It's crucial to be careful and have recent backups in case something goes wrong.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.