Point in Time with Differential Backup

Question

Post reply

Point in Time with Differential Backup

DBABC

SSCarpal Tunnel

Points: 4813
More actions
February 5, 2010 at 10:42 am

#210696

Hi,
Can we do point in time recovery with a differential backup?
Here is what i did
-Restore Full (taken at 02-04-10 9PM) with Norecovery
-Restore differential (taken at 02-05-10 12 AM) with STOPAT = '02-04-2010 22:10:00'
-Received the following error
Msg 4338, Level 16, State 1, Line 1
The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Please help, i dont have any tLog between 9PM and 12AM (they were created at 10:15 PM and 11:15 PM, but they have been deleted). I just have a full at 9 PM and differential at 12AM and i need to restore till 10:10 PM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

No, to complete a point in time restore you also need transaction log backups.

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 2

Thanks Lynn.

I took following code from BOL and its using StopAt with full backup, how come?

RESTORE DATABASE MyAdvWorks

FROM MyAdvWorks_1, MyAdvWorks_2

WITH NORECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM'

RESTORE LOG MyAdvWorks

FROM MyAdvWorksLog1

WITH NORECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM'

RESTORE LOG MyAdvWorks

FROM MyAdvWorksLog2

WITH RECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

UT- (2/5/2010)
Thanks Lynn.
I took following code from BOL and its using StopAt with full backup, how come?
RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1, MyAdvWorks_2
WITH NORECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM'
RESTORE LOG MyAdvWorks
FROM MyAdvWorksLog1
WITH NORECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM'
RESTORE LOG MyAdvWorks
FROM MyAdvWorksLog2
WITH RECOVERY, STOPAT = 'Apr 15, 2005 12:00 AM'

First, don't know why BOL would do it that way. Second, it you look closely there are two RESTORE LOG commands following that RESTORE DATABASE. The only one of the three commands shown that really require the STOPAT is the last RESTORE LOG command.