July 15, 2012 at 5:52 pm
I have a question for backup/restore.
We did a manual full back up I will call it snapshot1 before we started to do some school year end processing.
Then the processing failed, I had to restore from the backup from snapshot1 and then we continued to do some processing and finished it.
But in between snaphot1 and the restore, there is another full backup happened,it is our regular weekend full backup schedule. I will call it snapshot2.
Now if I do a diffrential backup now, I guess it will backup all the differences following snapshot2 instead snapshot1.
But I restored to snapshot1.
If later if something wrong again and I want to restore to the year end finishing point, can I just restore snapshot2 and the diffrential backup and it will cover all we have changed. I don't think I can restore snapshot1 and the differential backup?
Thanks
Thanks
July 16, 2012 at 12:53 am
I almost sure that applying that dif backup to your full backup will be failed. But you really should try that. And we all will get an undoubt answer.
July 16, 2012 at 1:21 am
But in between snaphot1 and the restore, there is another full backup happened,it is our regular weekend full backup schedule. I will call it snapshot2.
Snapshot 2 backup has everything you might need. Another fact, FULL backup doesn't break the backup chain. Paul explains the same here...
Myth #20: after breaking the log backup chain, a full database backup is required to restart it.
July 16, 2012 at 3:52 am
The diff will be based on the backup that you restored.
So let's say you have this scenario:
Full backup 1
Full backup 2
Restore of Full Backup 1
Differential backup
Then to restore the diff, you would restore Full Backup 1 (with no recovery) and then the differential. If you attempted to restore Full Backup 2 and then the diff, you'd get this error:
Msg 4330, Level 16, State 3, Line 7
This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.
Dev: Paul's blog is specifically restarting the log backup chain after something like a switch to simple recovery, not about restore paths and diff backups or restore paths and log backups, and it's also not the blog post that shows that full backups don't break the log backup chain.
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
July 16, 2012 at 10:54 am
Yesterday because of time limititation, to make sure I made a full backup again instead of differential.
Today I have time to test my question posted above, I created a very simple database for this purpose.
It turns out Gail's answer is absolutely correct.
I have to use snapshot1 instead of snapshot2 and the differential.
But in theory, I am not sure why it is that, I thought the differenital is alwasy based on the last full backup.
Gail, could you explain in a simpler way why snapshot1 is used please?
I read SQL bookonline recovery path, it has so much information there, I couldn't understand by just read it briefly
July 16, 2012 at 10:59 am
In short because you restored the database. There's no way on earth that the diff could have been based off the second full backup, because in the context of the backup you restored, the second full backup never happened.
You restore the database to a point before the second full backup happened and then took a diff. The diff couldn't have been based on a backup that never happened to that database (it happened to some other database that no longer exists).
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
July 16, 2012 at 11:05 am
GilaMonster (7/16/2012)
In short because you restored the database. There's no way on earth that the diff could have been based off the second full backup, because in the context of the backup you restored, the second full backup never happened.You restore the database to a point before the second full backup happened and then took a diff. The diff couldn't have been based on a backup that never happened to that database (it happened to some other database that no longer exists).
Thanks, what does this mean-in the context of the backup you restored?
Where usually recovery path is marked, in the restored database itself, or somewhere else?
July 16, 2012 at 11:07 am
sqlfriends (7/16/2012)
GilaMonster (7/16/2012)
In short because you restored the database. There's no way on earth that the diff could have been based off the second full backup, because in the context of the backup you restored, the second full backup never happened.You restore the database to a point before the second full backup happened and then took a diff. The diff couldn't have been based on a backup that never happened to that database (it happened to some other database that no longer exists).
Thanks, what does this mean-in the context of the backup you restored?
Where usually recovery path is marked, in the restored database itself, or somewhere else?
and is the statement wrong: the differential backup is always based on the last full backup
July 16, 2012 at 11:14 am
It would help if you provided a more definitive timeline of when your backups and restores occurred. From there we could probably help you understand how the backups relate and how they affect your restores.
July 16, 2012 at 11:36 am
sqlfriends (7/16/2012)
Thanks, what does this mean-in the context of the backup you restored?
Exactly that, in the context of the database restored from that backup.
Think about it.
You took backup 1, let's say at 10am
You then took backup 2, let's say at 11am
Later you restored backup 1. Now that database is exactly as it was at 10am. As far as that restored database is concerned, nothing after 10am happened (because nothing that happened after 10am was in the backup), hence the 11am backup never happened to it.
When you then took a diff, it was based on the latest full backup taken of that database, the one at 10am. The fact that some other database of the same name had a 11am backup is utterly irrelevant, the database that you are taking a differential backup of had a full backup at 10am and no other.
Where usually recovery path is marked, in the restored database itself, or somewhere else?
Database header page and backup headers.
and is the statement wrong: the differential backup is always based on the last full backup
No, it's perfectly correct.
Differential backup is always based on the last full backup taken of the database.
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
July 16, 2012 at 12:08 pm
GilaMonster (7/16/2012)
The diff will be based on the backup that you restored.So let's say you have this scenario:
Full backup 1
Full backup 2
Restore of Full Backup 1
Differential backup
Then to restore the diff, you would restore Full Backup 1 (with no recovery) and then the differential. If you attempted to restore Full Backup 2 and then the diff, you'd get this error:
Msg 4330, Level 16, State 3, Line 7
This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.
Dev: Paul's blog is specifically restarting the log backup chain after something like a switch to simple recovery, not about restore paths and diff backups or restore paths and log backups, and it's also not the blog post that shows that full backups don't break the log backup chain.
I am sorry I missed intermediate restore of the database. Thus I suggested Snapshot 2 has everything you might need.
Full backup 1
Full backup 2
Restore of Full Backup 1
Differential backup
I referred Paul’s blog just as an FYI. I assumed OP was worried if he will lose the backup chain if can't figure out where exactly the differential belongs to. Thanks for clarifying it anyways.
Snippet from Paul's blog:
The myth says that a full database backup is required to restart the log backup chain. In reality, all I need is a data backup that bridges the LSN gap. A differential backup will do:
BACKUP DATABASE LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_Diff1.bck' WITH INIT, DIFFERENTIAL;GO
BACKUP LOG LogChainTest TO DISK = 'C:\SQLskills\LogChainTest_log2.bck' WITH INIT;
GO
Processed 40 pages for database 'LogChainTest', file 'LogChainTest' on file 1.
Processed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.083 seconds (4.040 MB/sec).
Processed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.010 seconds (0.768 MB/sec).
This is really cool because you don't need to take a (potentially very large) full database backup to be able to continue with regular log backups.
July 16, 2012 at 12:14 pm
GilaMonster (7/16/2012)
sqlfriends (7/16/2012)
Thanks, what does this mean-in the context of the backup you restored?Exactly that, in the context of the database restored from that backup.
Think about it.
You took backup 1, let's say at 10am
You then took backup 2, let's say at 11am
Later you restored backup 1. Now that database is exactly as it was at 10am. As far as that restored database is concerned, nothing after 10am happened (because nothing that happened after 10am was in the backup), hence the 11am backup never happened to it.
When you then took a diff, it was based on the latest full backup taken of that database, the one at 10am. The fact that some other database of the same name had a 11am backup is utterly irrelevant, the database that you are taking a differential backup of had a full backup at 10am and no other.
Where usually recovery path is marked, in the restored database itself, or somewhere else?
Database header page and backup headers.
and is the statement wrong: the differential backup is always based on the last full backup
No, it's perfectly correct.
Differential backup is always based on the last full backup taken of the database.
July 16, 2012 at 12:14 pm
This is clear to me now, thanks for the good explanation.
July 16, 2012 at 12:18 pm
Following information from backupset system table would be helpful.
differential_base_lsnnumeric(25,0)
Base LSN for differential backups. For a single-based differential backup; changes with LSNs greater than or equal to differential_base_lsn are included in the differential backup.
database_backup_lsn numeric(25,0)
Log sequence number of the most recent full database backup. Can be NULL.
database_backup_lsn is the “begin of checkpoint” that is triggered when the backup starts. This LSN will coincide with first_lsn if the backup is taken when the database is idle and no replication is configured.
More: http://msdn.microsoft.com/en-us/library/ms186299(SQL.105).aspx
July 16, 2012 at 12:22 pm
Thanks, this is very helpful information, I will find a time to read and study more into it.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply