HELP: Point in time restore : HOW ? -- sql server programming

GYK
10/19/2004 3:15:06 PM

Hi Pagus,

Point in time recovery can be done with tlogs..... had you taken tlogs after
15:00 full backup?

Thanks
GYK

[quoted text, click to view]

Pagus
10/19/2004 11:22:39 PM

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

rgn
10/20/2004 12:49:03 AM

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

[quoted text, click to view]

Greg D. Moore (Strider)
10/20/2004 6:02:20 AM

[quoted text, click to view]

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. :-)

[quoted text, click to view]

wumutek NO[at]SPAM yahoo.com
10/20/2004 11:11:00 AM

[quoted text, click to view]

Always used transaction logs to do a point in time restore.

Tibor Karaszi
10/20/2004 6:30:15 PM

[quoted text, click to view]

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/

[quoted text, click to view]

mike.metcalf NO[at]SPAM 4projects.com
10/29/2004 7:35:08 AM

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

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : HELP: Point in time restore : HOW ?