Concurrent backups (full and transaction)

aultmike

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?

petey

A full database backup will usually include all transactions logged until the end of the full backup, but there is a small window between the time the full backup is checkpoint-ed, and the time the backup file is finalized. It's usually safe to just restore all transaction log backups that were make after the full backup.

The more accurate way would be to compare the LSN values for the backups. This takes a bit more work to work through the backup history tables/files to determine the right sequence of files to restore.

Then there's always the SQL Backup LATEST_ALL option. If you're planning to refresh the database every morning, the LATEST_ALL option would pick up the fastest sequence of files to restore to give you the latest possible state of the database. E.g.

EXEC master..sqlbackup '-sql "RESTORE DATABASE AdventureWorks LATEST_ALL FROM DISK = [g:ackupsAW*.sqb]"'

SQL Backup will then pick up the best possible combination of full, differential, and transaction log backup files from the given folder to restore the database. See https://documentation.red-gate.com/disp ... RE+command for further details.

aultmike

Peter,

Thanks for the reply! I DIDNT know about that feature in the RESTORE command but I will look into it! My intent isnt to do a full restore every morning but rather do a full restore and then apply log backups indefinately.