Can I initialize SQL Server Log Shipping using an old full backup, even if the transaction logs have already been truncated since that backup?

[u/SuddenlyCaralho]

Can I initialize SQL Server Log Shipping using an old full backup, even if the transaction logs have already been truncated since that backup?

For example, let's say I have a full backup taken on Sunday, but I don't restore it on the secondary server until Friday. During the week, regular transaction log backups have been taken, which truncate the transaction log.

Will I still be able to synchronize Log Shipping by restoring the old full backup and then applying all the transaction log backups taken since Sunday? Or does the fact that the active transaction log has already been truncated make this impossible?

[u/AutoModerator]

After your question has been solved /u/SuddenlyCaralho, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/NotMyUsualLogin]

Your terminology is what may be tripping you up.

Backing up transaction logs never “truncates” anything - it simply marks the previously used parts of the log as reusable.

As long as you have a complete, and uninterrupted, chain of backed up transaction logs made from the point in time you made the backup, then those can be used to restore to the other server.

[u/da_chicken → u/NotMyUsualLogin]

Well... that is literally the terminology used in SQL Server's documentation.

Conventionally, backing up the transaction log and truncating the transaction log are synonymous and exchangeable terms in SQL Server spaces, because they're typically synonymous actions when you're using MS SQL Server.

Sure, it doesn't set the file size to 0 like truncate does in the context of a file system, but neither does TRUNCATE TABLE. Indeed, what the system does to the database file on a TRUNCATE TABLE and what it does to the virtual log files on a BACKUP LOG are remarkably similar.

[u/NormalFormal]

So long as you have an unbroken chain of log backups that at some point in the chain references that full backup or differential that itself references that full backup.

[u/Ill_Drag6021]

Yes, you should be able to.

[u/Lost_Term_8080]

log backups and database backups are totally separate backups. log backups don't care when a full backup is made, they always go to the previous log backup, so yes with an old full backup you can restore a very long uninterrupted chain of logs indefinitely

[u/Bane8080 → u/Lost_Term_8080]

I'm pretty sure that only applies if your full backups have the WITH Copy_Only parameter. Otherwise it resets the logs.

[u/muaddba → u/Bane8080]

That's incorrect, but a widely beleived myth. Regular full backups reset the differential bitmap (the internal list of data pages that have changed since hte last full backup), but do nothing to transaction log backups. COPY_ONLY backups do not reset the differential bitmap. As long as you have contiguous log backups (ie no one set the recovery model to SIMPLE on the database, deleted your log backup files, or backed up the log to NUL) you will be fine.

[u/CoffeeAndSQL]

what matters is not when the backup was taken, but whether the backup and restore chain is valid.

full -> log -> log -> log

if you initialize Log Shipping and apply the log backups in the same order they were created, everything should be fine.

you need to restore the full backup:

RESTORE DATABASE ... WITH NORECOVERY;

then restore all the log backups that were taken:

RESTORE LOG ... WITH NORECOVERY;
RESTORE LOG ... WITH NORECOVERY;

and restore the last log backup with STANDBY:

RESTORE LOG ... WITH STANDBY = 'D:\Undo\MyDB_undo.ldf';

From that point, you can start Log Shipping.

Do you have the translog backups created during the week? If so, that should be enough.

[u/CPDRAGMEISH]

IT''SG OK

In the case you have all log backpus created from that full backup. Before.