February 28, 2008 at 2:09 pm
I have inherited the following scenario:
1) Full backup using NetBackup Agent at 5:00 am
2) Full backup to remote server using SQL at 6:00 am
3) Log backups every hour beginning at 7:00 am until 3:00 am using Netbackup Agent
Question:
Can I restore the log backups on top of a restore of either full backup or can these only be applied to the restore of the last full backup. I've never had this situation in the past and I'm thinking it can only be applied to #2. But a peer is 99% sure that it can be appied to either. I guess we're looking to be 100% sure so we know how to proceed for D/R purposes. Thanks.
-- You can't be late until you show up.
February 28, 2008 at 2:15 pm
Logs can be applied to a DB restored from any full backup.
You have to have all of the logs, so you have to apply more logs for the older full backup.
February 28, 2008 at 2:27 pm
In this case the first log backup is after both full backups. So, technically, it would be the same number of logs files applied to either restore of the full backups. True?
Now let me pose this to you. What if there were committed inserts/updates between the completion of the first full backup and the start of the second full backup but the log wasn't backed up until after the second full backup. The second full backup would have some committed transactions in it that would also be in the first log backup, right? If so, what happens if I restore the second backup (with commited transactions applied) and then restore the log file, which also has those same transactions in it? Am I over-analyzing this issue?
-- You can't be late until you show up.
February 28, 2008 at 2:55 pm
I usually recommend running log backups much more often; every 15 minutes, 24x7, is a good place to start.
As long as you apply all the log backup files in the correct sequece, you will be fine.
February 28, 2008 at 3:05 pm
We have low volume of transactions here and a user "loss of data" threshold of one hour so the hourly log backups work fine (for now). I appreciate the quick response. I'll rest a little easier this evening knowing we're OK. Thanks again for your help.
-- You can't be late until you show up.
February 28, 2008 at 4:41 pm
tosscrosby (2/28/2008)
We have low volume of transactions here and a user "loss of data" threshold of one hour so the hourly log backups work fine (for now). I appreciate the quick response. I'll rest a little easier this evening knowing we're OK. Thanks again for your help.
It really doesn't cost more in system resources to run the log backups more often.
Also, if you have any processes like reindexing, index defragmentation, or bulk imports running, it can cause the size of the transaction log file to grow quickly if you do not have frequent log backups.
March 5, 2008 at 7:54 am
If so, what happens if I restore the second backup (with commited transactions applied) and then restore the log file, which also has those same transactions in it?
SQL Server knows which transactions need to be applied, and which can be ignored. It does this by the use of log sequence numbers (LSN). If you run the RESTORE HEADERONLY command on your backup file, there are 4 LSN values displayed. SQL Server uses the CheckpointLsn value to determine the point from which recovery should start i.e. transactions need to be applied.
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