May 13, 2011 at 8:07 am
Scenario:
Full Backup taken at 3:00 am - Duration 30 minutes
Transaction log backup 1 taken at 3:05am Duration 1 minute
Transaction log backup 2 taken at 3:35am Duration 1 minute.
To complete a restore of the above backup files do I need to:
A) Restore the full backup followed by transaction log backup 1 and finally transaction log backup 2?
OR
B) Restore the full backup followed by transaction log backup 2?
Thanks
May 13, 2011 at 8:20 am
Wrong question (no offense).
When did the disaster happen? To what point in time do you want to restore?
The full backup contains all the page at the time the backup finished. I've never actually tested the scenario you have in question but I'm sure the server would either throw an error saying it can't use log 1 or just not be able to use and move on to the next line of code.
May 13, 2011 at 8:41 am
No disaster involved. More of a general question related to a restore sequence we intend carrying out in the near future as part of an migration/upgrade.
Will take a tail log backup from the source database as part of the migration/upgrade.
In particular we were curious about the transaction log backup that took place during the full database backup.
Thanks for your response.
May 13, 2011 at 8:49 am
My pers preference... adapt to your needs.
Take full backup during off hours. Move copy and restore with norecovery on 2nd server
When you're getting close to the move copy take a diff, move and restore too.
Then take the tail. That should give you minimal downtime.
May 13, 2011 at 9:08 am
You should restore the full, then try log 1, then try log 2. It may not need log 1, but SQL will let you know. No harm done in trying.
Actually, just tested. Log 1 would give you an error, which is easy to handle, but you shouldn't get worried.
The way backup works, IIRC, in your situation is:
- Full backup starts at 3:00am
- Log backup starts
- Full backup completes data pages at, say, 3:25. This is a guess. At this point, that is the time at which your backup is currect. Now all log pages from 3:00am to 3:25 are added to the backup. These contain committed transactions (roll forward) and uncommitted transactions (roll back), which are applied on restore.
- At 3:30, your backup completes, but it essentially is as of the time the data pages were completely copied out.
- Log backup 2 at 3:35 runs. This contains transactions from 3:05 onward, some of which are in the full backup. However SQL Server sorts this out and will only apply the log records from 3:25 on when this log backup is restored.
Make sense?
May 13, 2011 at 9:28 am
Always err on the side of restoring more log backups, not less. If in your case log backup 1 wasn't needed and you restored full, log 1, log 2, log one would give an error, log 2 would restore, DB would be left at intended time. If log 1 was required and you just tried to restore 2, that would throw an error and not leave the DB at the intended time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2011 at 9:44 pm
The backup process is as Steve described, except for point 3
Now all log pages from 3:00am to 3:25 are added to the backup.
which will depend on whether there were any open transactions at 3:00 am and also if replication was in use.
See Paul Randal's entry on this.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply