HELP: Point in time restore : HOW ?

I have full backup of database at 13:00
and another full backup at 17:00.
I've made backup of transaction log at 17:05
When I try to restore database to state at
15:10 (point in time) , the dialogue in Enterprise Manager
says that only time after 17:05 is valid.
It seems to me that I've done something wrong at 17:05 while taking
trans. log backup.
But, again, if I have full backups at 13:00 and 17:00 restoring
database to point in time at 15:10 should be possible ?!
Any help is appreciated.
PagusRestore the full backup from 13:00 with NORECOVERY
Restore the tLog backup to 15:10 with Recovery
Should work.
Rick
"Pagus" <pagus@.writeme.com> wrote in message
news:5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||Hi Pagus,
Point in time recovery can be done with tlogs.... had you taken tlogs after
15:00 full backup?
Thanks
GYK
"Pagus" wrote:
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
> Restore the full backup from 13:00 with NORECOVERY
> Restore the tLog backup to 15:10 with Recovery
I have not done this, but you may want to restore tlog backup to 15:10 with
a standby file.
I THINK you can then restore in time past 15:10 again w/o having to do the
full restore.
(hmm, I should try this now that I think about... too tired right now
though. :-)
> Should work.
>
> Rick
>|||To summarize what u currently have :
[1] First Full Database Backup @. 13:00
[2] Second Full Database Backup @. 17:00
[3] TLog @. 17:05
Trying to Point-In-Time restore to 15:10
Considering that the Point-In-Time restore is before the second DB backup,
the Second DB backup is irrelevent.
There are a few things that u shud check
[1] Was the DB in valid state when you issued the Backup @. 13:00
[2] Check the duration (Period) for the TLog backup. It should have an
end time of 17:05 and start time of what ?
Thanks,
Gopi
"Pagus" wrote:
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
>|||> (hmm, I should try this now that I think about... too tired right now
> though. :-)
No need to Greg. I have a demo script just for this. You can restore the same tlog several times,
going forward in time, using STANDBY. I also checked with MS and this is a tested and supported
method. Here are my comments on the topic:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Mbndd.312510$bp1.26137@.twister.nyroc.rr.com...
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OzkXPNitEHA.3200@.TK2MSFTNGP09.phx.gbl...
>> Restore the full backup from 13:00 with NORECOVERY
>> Restore the tLog backup to 15:10 with Recovery
> I have not done this, but you may want to restore tlog backup to 15:10 with
> a standby file.
> I THINK you can then restore in time past 15:10 again w/o having to do the
> full restore.
> (hmm, I should try this now that I think about... too tired right now
> though. :-)
>
>> Should work.
>>
>> Rick
>>
>|||Pagus <pagus@.writeme.com> wrote in message news:<5q0bn0ttoavi4q60tgfqvp4ruqa0hnopmp@.4ax.com>...
> I have full backup of database at 13:00
> and another full backup at 17:00.
> I've made backup of transaction log at 17:05
> When I try to restore database to state at
> 15:10 (point in time) , the dialogue in Enterprise Manager
> says that only time after 17:05 is valid.
> It seems to me that I've done something wrong at 17:05 while taking
> trans. log backup.
> But, again, if I have full backups at 13:00 and 17:00 restoring
> database to point in time at 15:10 should be possible ?!
> Any help is appreciated.
> Pagus
Always used transaction logs to do a point in time restore.
Vincento|||Hi there,
a point in time restore is only possible when using a transaction log
to do the restore. I know it probably doesnt help you but for future
reference here is the way to do it.
first restore the full backup leaving the database in no-recovery mode
then restore the transaction log backup to the point in time that you
want, this time recoverying the database after the restore completes.
Depending upon the size of your database it might be worth looking at
doing a full backup just once per day and several transaction log
backups throughout the day every hour, or couple of hours.