Sql-server – Concurrent backups (full and transaction) and testing recovery model

backuprecovery-modelsql serversql-server-2012transaction-log

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?

Best Answer

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.