Posted by Erland Sommarskog on 05/25/05 23:32

(Ty.Incog@gmail.com) writes:
> Let's throw log shipping and clustering out of the conversation. Is
> there no automated way for me to restore a transaction log. Lets say a
> company only does one full backup a week on Sunday at midnight, but
> backs up the transaction log every 10 minutes. The database farts and
> dies on Saturday. That means there are approximately 6 logs/hr x 24
> hr/day x 6 days =864 logs to restore. Microsoft provided no way to
> apply these logs? The only way I can see to accomplish this is using
> RESTORE HEADERONLY to find the base LSN from the db backup, then using
> RESTORE HEADERONLY on the log backup and iterating through each record
> in the result set that has an LSN usable based upon the backup LSN.
>
> Sounds like multiple days of coding and tinkering to get that script
> working. Does anyone have a script already written? Does good 'ol
> Mircosoft provide one?

Since I rarely have to restore transaction logs, I might be missing
something, but there is a screen in Enterprise Manager which looks
spot on target. Right-click databases, select All tasks, and then
Restore. In the dropdown "Show all backups for all databases". Select
the database you want to restore backups for. In the box below, the
full backups and the log backups will appear (and I assume the
differential.)

I will have to admit never having tried it, but I'm pretty sure that
I did something like this in 6.5 days, and I can't see why MS would
drop such a feature.

If you want to look at scripts, this could be a starter:
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]