June 4, 2012 at 7:58 am
During the restore, there is the option to 'Restore to the most recent state posible'.
Question 1:
When using a full backup and no transactions backups,
is this the only point you can restore to ?
Question 2:
During the backup does the RDBMS tries to make the backup in such a way that the most recent state is as late possible?
As I understand it, during the backup all commits which occure before the backup is ended are included and the restore normaly is to the latest commit which was done during the backup.
Is the correct?
Or am I missing some subtle difference?
Or am I completely mistaken?
(An alternative could be a snapshot is started at the start of the backup and this snapshot is taken as a backup).
Thanks for your time and attention.
Ben Brugman
June 4, 2012 at 8:57 am
When all you have available is the full backup, that's all you're going to get. If you have log backups available, you can restore right to the end of the log backup chain, or you can use the STOPAT command to specify a moment in time. It really just depends on what you have available from your backups.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2012 at 9:10 am
To build on Grant's note.
The RDBMS always does a recovery when you restore. This means that all commits in the log are verified as being in the data files as well. If they are not there, they are applied to the data files and things are "rolled forward" to match the commits. After that, all items that were uncommitted, but partially in the logs, are undone in the data files so that you have a rollback of uncommitted actions. This ensures integrity of the data.
When you restore, the option to the latest state is a shortcut with logs. It goes to apply all the restores it can. The recovery still takes place at the end of the restore.
June 4, 2012 at 9:15 am
Grant Fritchey (6/4/2012)
When all you have available is the full backup, that's all you're going to get. If you have log backups available, you can restore right to the end of the log backup chain, or you can use the STOPAT command to specify a moment in time. It really just depends on what you have available from your backups.
Thank you for your reply.
This does anwser question one. There is only one point in time to restore to.
This leaves question two.
Suppose the backup lasted for 2 hours. Is the backup then the situation from the beginning, the end, the middle, totaly unknown?
I would guess that in general the backup is somewhere near the end, offcourse without any open transactions. But this is me guessing.
So, when a backup is made the restore point in time will be near to:
1. The start of the backup.
2. Somewhere in the middle of the backup time.
3. Somewhere near the end of the backup time.
4. Totaly unknown.
5. In General near the start. (With some exceptions ??)
6. In General near then end. (With some exceptions ??)
We would like to know this because we try to synchronise different systems, some propriety, some messaging etc. So knowing what the backup does helps makes better planning possible and reduces overhead.
Thanks for your time and attention.
Ben Brugman
June 4, 2012 at 9:27 am
ben.brugman (6/4/2012)
Suppose the backup lasted for 2 hours. Is the backup then the situation from the beginning, the end, the middle, totaly unknown?
Near the end. To be specific, it'll be a consistent state as of the time that the data-reading portion of the backup completed.
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
June 4, 2012 at 9:30 am
ben.brugman (6/4/2012)
Grant Fritchey (6/4/2012)
When all you have available is the full backup, that's all you're going to get. If you have log backups available, you can restore right to the end of the log backup chain, or you can use the STOPAT command to specify a moment in time. It really just depends on what you have available from your backups.Thank you for your reply.
This does anwser question one. There is only one point in time to restore to.
This leaves question two.
Suppose the backup lasted for 2 hours. Is the backup then the situation from the beginning, the end, the middle, totaly unknown?
I would guess that in general the backup is somewhere near the end, offcourse without any open transactions. But this is me guessing.
So, when a backup is made the restore point in time will be near to:
1. The start of the backup.
2. Somewhere in the middle of the backup time.
3. Somewhere near the end of the backup time.
4. Totaly unknown.
5. In General near the start. (With some exceptions ??)
6. In General near then end. (With some exceptions ??)
We would like to know this because we try to synchronise different systems, some propriety, some messaging etc. So knowing what the backup does helps makes better planning possible and reduces overhead.
Thanks for your time and attention.
Ben Brugman
http://voiceofthedba.wordpress.com/2011/09/01/sql-server-backups-when-is-it-current/
June 4, 2012 at 9:39 am
GilaMonster (6/4/2012)
Near the end. To be specific, it'll be a consistent state as of the time that the data-reading portion of the backup completed.
Thanks,
this wil help us with planning the backups on different systems.
Ben
June 4, 2012 at 9:54 am
If you need to sync restores of multiple databases, then you need log backups, marked transactions and a RESTORE ... WITH STOPBEFOREMARK
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply