January 4, 2011 at 12:33 am
Hi ,
I am a beginer to sql server..so i have a doubt regarding recovery model ..why point in time restoration not posible when you set bulk recovery to database and what type of information exactly stored in log when you set as bulk recovery model
Thanks
Rekha.
January 4, 2011 at 12:43 am
Point-in-time is generally possible in bulk-logged recovery. The only time when it's not is when there are bulk operations within the transaction log backup that covers that time period.
So, if we have something like this (in bulk-logged recovery only)
10:00 log backup
10:30 log backup
10:40 bulk insert
10:50 bulk insert
11:00 log backup
11:30 log backup
With that, it's possible to restore to any point between 10:00 and 10:30, any point between 11:00 and 11:30, but not to a point within the 10:30-11:00 time frame because of the bulk-insert.
The reason here is that in bulk-logged, the changes to the data caused by the bulk insert are not fully logged. When there's a log backup, SQL compensates by putting all of the pages changed by the bulk insert into the transaction log, but it's the pages as they were at 11:00, not as they were at 10:42:05 when the first bulk insert finished. Hence it cannot restore the database to 10:45, because it's only got the pages as they were at 11:00, not as they were at 10:45.
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
January 4, 2011 at 12:45 am
Thanks fro reply
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply