What columns/order transaction logs to use in Point in Time recove - 08-28-2009 , 03:37 PM
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:
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
Re: What columns/order transaction logs to use in Point in Time recove - 08-28-2009 , 04:11 PM
JB (JB (AT) discussions (DOT) microsoft.com) writes:
don't need it, but SQL Server should tell you. The last log is the one
that has a FinnishDate after the mistake.
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
Re: What columns/order transaction logs to use in Point in Time recove - 08-29-2009 , 03:09 AM
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
Tibor Karaszi, SQL Server MVP
"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote