Restore to point in time !! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Restore to point in time !!

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2009-02-08 : 07:57:13

if i restored full backup and then seq log backups then i wanted to restore point in time i can restore to point where the date would be only the date after the full backup i.e only the date and time during the log backups.
because i tried to restore to point during the full backup range it gave successful but when restoring with recover it gave to the last log backup restore

ex RESTORE LOG Efiling
FROM disk = 'E:\Efiling_log_backup_device.bak'
WITH NORECOVERY, STOPAT = '20080101 2:00 PM'
gave
restored successfully ...
then i issued
RESTORE DATABASE efiling WITH RECOVERY

restore point was 20090208 3:00pm !!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-08 : 08:22:54

[code]--RESTORE DATABASE... WITH NORECOVERY...

--RESTORE LOG Efiling
FROM disk = 'E:\Efiling_log_backup_device.bak'
WITH RECOVERY, STOPAT = '20080101 2:00 PM'[/code]

ann06
Posting Yak Master

171 Posts

Posted - 2009-02-09 : 05:56:51

I tried still same result
--RESTORE LOG Efiling
FROM disk = 'E:\Efiling_log_backup_device.bak'
WITH RECOVERY, STOPAT = '20080101 2:00 PM'

RESTORE LOG successfully processed 0 pages in 9.000 seconds (0.000 MB/sec)

when checking I find it restored to the latest transaction, so again i want to restore point in time i can restore to point where the date would be only the date after the full backup i.e only the date and time during the log backups?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-09 : 19:46:34

Then apply Fullbackup before that time in Norecovery mode and next log backup with STOPAT option.

ann06
Posting Yak Master

171 Posts

Posted - 2009-02-10 : 01:35:54

the idea its not clear yet for me!
the restore for a specific point in time should be only for the time that is covered only by this log iam restoring?
ex:

1st log_backup time elapsed 1:00 pm --> 2:00 pm
2nd log_backup time elapsed 2:00 pm --> 3:00 pm

1. Applied 1st log with no recovery
2. Applied 2nd log with stopat 1:30 pm (cuz i dont know the time covering for each log so at the final log i will specify the intended time)

will this will restore to the 1:30 pm?

ann06
Posting Yak Master

171 Posts

Posted - 2009-02-10 : 08:42:50

?? any help where is the guys !!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-10 : 10:22:19

Then you should use STOPAT option while recovering for Ist Log for 1:30 p.m.

ann06
Posting Yak Master

171 Posts

Posted - 2009-02-11 : 08:50:48

but i always overwrite the log backup file.
so i can't restore to the time where it was covered by the previous log backups?