March 15, 2007 at 1:20 am
hi there,
western australia is about to go through its first change from daylight savings time to non-dst. we have been trialling the possible impacts and have found some unusual results when restoring a transaction log to a point in time.
firstly, let's imagine the following sequence of events:
00:00 - full backup taken (file 1)
01:00 - hourly trn taken (file 2)
01:55 - manual job run to take trn log before daylight savings ends (file 3)
01:59 - clock is about to revert back to 1am as daylight savings ends
01:00 - clock has just reverted back to 1am
02:00 - hourly trn log taken (file 4)
now let's say we are asked to restore a copy of the databaseup to its state as at the first time it was 1:55. we do the following:
(1) restore full backup with norecovery
(2) restore 1am trn file with norecovery
(3) restore 1:55am trn file with recovery
this works fine - we get all records up to and including those in trn file 3 ie up to 1:55am
but now let's say we are asked to restore the database to the point in time which happens to be the second time it was 1:45 am. we do the following:
(1) restore full backup with norecovery
(2) restore 1am trn file with norecovery
(3) restore 1:55am trn file with norecovery
(4) restore 2am trn file with recovery & stopat 1:45am
this does not work. we have found that we only get records up to the first 1:55 ie from file 3. we find that there are no records from trn file 4 even though we asked for all records from trn file 4 up to the repeated 1:45am
to test that the records are actually in the log files, let's say we are asked to restore a copy of the databaseup to its state as at 2am (ie restore up to and including file 4). we do the following:
(1) restore full backup with norecovery
(2) restore 1am trn file with norecovery
(3) restore 1:55am trn file with norecovery
(4) restore 2am trn file with recovery
this works fine - we get all records up to and including those in trn file 4 ie up to 3am
any ideas?
March 19, 2007 at 5:31 am
Hi,
My guess here for this scenario you have mentioned is :
===================
let's say we are asked to restore the database to the point in time which happens to be the second time it was 1:45 am. we do the following:
(1) restore full backup with norecovery
(2) restore 1am trn file with norecovery
(3) restore 1:55am trn file with norecovery
(4) restore 2am trn file with recovery & stopat 1:45am
this does not work. we have found that we only get records up to the first 1:55 ie from file 3. we find that there are no records from trn file 4 even though we asked for all records from trn file 4 up to the repeated 1:45am
===================
Your point in time recovery is 1:45 am, and your 1:55 am trn backup file has whatever need to be recovered till 1:45 am. You got everything.
And coming to your 2 am trn backup file, it has nothing to recover because it will have all transactions from 1:55 am to 2 am. When you backup a trn log, it purges all inactive transactions (which are already committed to DB) and resets the LSN. This is the reason your "restore with recovery" is not recovering anything even it is showing success.
It seems your daylight saving changes are not creating any effect on the log backups. Check with Microsoft documention for Windows and SQL Server.
Hope this is helpful to you.
Anil
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply