Hello

I am using the full recovery model and I make transaction log backups
every 15 minutes. Now I need to restore the database because of user update
error. When I run the command:

use master
RESTORE HEADERONLY
FROM LgBackupLog

It shows 14 transaction logs that have been backed up. The columns
BackupStartDate and BackupFinishDate have a Date/Time. I assume that I use
the BackupFinishDate column to restore the database for Point-in-Time
recovery, my question is:

1-Out of the 14 transaction logs which is the first one to restore and which
is the last one to restore?

2-Do I only include the StopAt clause on the last transaction log that I
apply?

JB
--
JB

JB (JB (AT) discussions (DOT) microsoft.com) writes:
The first is the oldest. Well, if it's older than the full backup, you
don't need it, but SQL Server should tell you. The last log is the one
that has a FinnishDate after the mistake.

A quick look in Books Online tells me that you should be able to specify
it on all RESTORE commands. "If the specified STOPAT time is after the last
LOG backup, the database is left in the unrecovered state, just as if
RESTORE LOG ran with NORECOVERY."

A Google search on "Restoring a Database to a Point Within a Backup"
maybe helpful for you.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Yes, as of 2005. For all but the "last one" (where we actually will
stop), this is just to let SQL Server know out intention. Imagine you
have the following:

a, 10:00 log
b, 11:00 db
c, 12:00 log
d, 13:00 log

Now, if my intention is to stop at 09:45, clearly I need some db
backup prior to a. If I bymistale restore b, c and then do STOPAT
10:45 for d, I would have wasted time on restoring b and c. By
specifying STOPAT for all restore command, SQL Server would notify me
for my first restore (b) that I need to go hunt for some earlier
backup.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote