Recover Transaction Log

Hi - I'm requesting help to understand a concept:
Can you restore a Transaction Log Backup only Transaction Logs backed up
AFTER a Full Backup?
EXAMPLE: Setup assuming 2 hard drives:
* Database MDF files stored on hard drive (A)
* Database Transaction Log files soted on hard drive(B)
First, Lets assume a Full Backup has been peformed nightly for a week at
Midnight.
Second, lets also assume that a week ago I temporaily stoped the
every-2-hour-schedule for a Transaction Log Backup and forgot to turn the
schedule back on - Therefore no Transaction Log Backup occured during the
past week.
Now, lets assume that harddrive (A) crashes at noon today - but hard drive
(B) is ok and still works. I have a succesfull Full Backup at Midnight, so I
know I can restore that.
Question: After Restoring from the Midnight Full Backup, Can I use the
current Transaction Log file as of Noon to restore the remainder of the logs
,
even though it had not been backuped for 6 days? Or is it required that a
transaction log backup have occured at some point after the Full backup?> Question: After Restoring from the Midnight Full Backup, Can I use the
> current Transaction Log file as of Noon to restore the remainder of the lo
gs,
> even though it had not been backuped for 6 days?
No. The reason you can't do that is the when you do the restore, you will ov
erwrite the log records
in the ldf file. What you can do, is *before* you do the restore, backup the
log. You now have a
database backup and a log backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"labsRcool@.community.nospan" <labsRcoolcommunitynospan@.discussions.microsoft
.com> wrote in message
news:ACC73D9F-EE1A-4E2B-B25D-24F4927A8BC6@.microsoft.com...
> Hi - I'm requesting help to understand a concept:
> Can you restore a Transaction Log Backup only Transaction Logs backed up
> AFTER a Full Backup?
> EXAMPLE: Setup assuming 2 hard drives:
> * Database MDF files stored on hard drive (A)
> * Database Transaction Log files soted on hard drive(B)
> First, Lets assume a Full Backup has been peformed nightly for a week at
> Midnight.
> Second, lets also assume that a week ago I temporaily stoped the
> every-2-hour-schedule for a Transaction Log Backup and forgot to turn the
> schedule back on - Therefore no Transaction Log Backup occured during the
> past week.
> Now, lets assume that harddrive (A) crashes at noon today - but hard drive
> (B) is ok and still works. I have a succesfull Full Backup at Midnight, so
I
> know I can restore that.
> Question: After Restoring from the Midnight Full Backup, Can I use the
> current Transaction Log file as of Noon to restore the remainder of the lo
gs,
> even though it had not been backuped for 6 days? Or is it required that a
> transaction log backup have occured at some point after the Full backup?
>
>|||Thanks!
Follow up question:
If I do as you suggest, and Backup the current log, then I'm assuming the
process is as follows:
1)Restore Full Backup from the midnight backup
2)Restore the Log backup that I just "backed up" before the restore.
In regards to the Log restore, I'm assuming then, that the restore process
is smart enough to only include from the transaction log the transactions
that have occured since the Midnight backup...and will not attempt to
re-include(duplicate) the transaction over the last week. (i.e., it's now
just treated as a normal transaction log backup/restore as if it I had been
backing them up like I was supposed to ;)
Thanks!
"Tibor Karaszi" wrote:

> No. The reason you can't do that is the when you do the restore, you will
overwrite the log records
> in the ldf file. What you can do, is *before* you do the restore, backup t
he log. You now have a
> database backup and a log backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "labsRcool@.community.nospan" <labsRcoolcommunitynospan@.discussions.microso
ft.com> wrote in message
> news:ACC73D9F-EE1A-4E2B-B25D-24F4927A8BC6@.microsoft.com...
>|||Yes, that is correct. The database backup has all log records produced durin
g that database backup.
So, restore will copy in the data pages, and use the log records in the data
base backup to do the
REDO process. When you then restore the log backup, SQL Server will start th
e REDO operation from
the very last log record in the database backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"labsRcool@.community.nospan" <labsRcoolcommunitynospan@.discussions.microsoft
.com> wrote in message
news:0CED8BB9-8B1D-4E04-BD07-B9525D1A5037@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Follow up question:
> If I do as you suggest, and Backup the current log, then I'm assuming the
> process is as follows:
> 1)Restore Full Backup from the midnight backup
> 2)Restore the Log backup that I just "backed up" before the restore.
> In regards to the Log restore, I'm assuming then, that the restore process
> is smart enough to only include from the transaction log the transactions
> that have occured since the Midnight backup...and will not attempt to
> re-include(duplicate) the transaction over the last week. (i.e., it's now
> just treated as a normal transaction log backup/restore as if it I had bee
n
> backing them up like I was supposed to ;)
> Thanks!
> "Tibor Karaszi" wrote:
>