Concurrent backups (full and transaction) and testing recovery model

Question

My intent is to test my recovery model and setup log shipping to replicate a database on another server.

I have a database called AdventureWorks on a primary sql server. Presently SQL Backup performs a full backup on this database every morning @ 6am and on the average takes 3 1/2 hrs (210 minutes) to complete. A separate job runs every 15 minutes and performs a log backup on the same database.

So, I want to restore a full backup from earlier that morning and then all the log backups since that full backup completed how do I know which transaction log backups to select? Is there any danger to applying transaction log backups from 915am to present?

Answer 1

how do I know which transaction log backups to select?

You can find that out using msdb..backupset table. To automate that, you can use RestoreGene - TSQL or PowerShell version

This procedure queries msdb database backup history and database file details in master. It builds and returns RESTORE DATABASE commands as it's result set, it does not execute the commands.

You can do concurrent full and log backups BUT the log is not cleared when the log backup ends. The clearing of the inactive portion of the log is delayed until the full backup completes.

Also, the first log backup restored after a full or diff needs to have the data backup’s lastLSN + 1 contained within its LSN range.

Refer : backup myths

Answer 2

Working out which Log Backup to restore is all based on LSN's. You have to apply all log backups since the full (or differential, but you didn't mention them) backup, including log backups taken while the full backup was in progress.

There is no danger in trying to restore a log backup out of sequence, it just won't work. Kin answered above how to find the actual log backups you need to restore.

It's important that you have a continuous chain of log backup files. If you are missing one, or if it's corrupt then you can't restore anything after that point.