Backup/Restore scenario

Hi,
I'm confused about the role of tLogs in a restore scenario. Imagine the
following:
Database running 24/7 with a lot of user activity. All backups at midnight
Fri - Full backup
Mon - tLog backup
Tue - tLog backup
Wed - tLog backup
Thurs - tLog backup
Imagine the server blows up one Thursday morning.
Q1. If I restore the full backup from the previous Friday, and then the
tLogs for Mon, Tue, Wed, does that mean I've got everything except the last
few user updates early Thursday morning?
Q2. What if the developers had added a new table on Tuesday? Would this new
table exist in the restored version?
--
Gerry HickmanQ1 - When you apply the transaction log backups you will have all the
modifications that have happened up to the point of the most recent
transaction log that you applied.
Q2 - Yes, the table that is created on Tuesday will be capured within
Tuesday night's t-log backup. It will be "created" when you restore that
log backup.
If you have "lots" of user activity as you say you might want to issue
transaction log backups more often than once per day.
--
Keith Kratochvil
"Gerry Hickman" <gerry1uk@.netscape.net> wrote in message
news:u6ZvvygPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm confused about the role of tLogs in a restore scenario. Imagine the
> following:
> Database running 24/7 with a lot of user activity. All backups at midnight
> Fri - Full backup
> Mon - tLog backup
> Tue - tLog backup
> Wed - tLog backup
> Thurs - tLog backup
> Imagine the server blows up one Thursday morning.
> Q1. If I restore the full backup from the previous Friday, and then the
> tLogs for Mon, Tue, Wed, does that mean I've got everything except the
> last
> few user updates early Thursday morning?
> Q2. What if the developers had added a new table on Tuesday? Would this
> new
> table exist in the restored version?
> --
> Gerry Hickman
>|||Hi Keith,
Thanks, this is very helpful and this was how I originally understood it
would work, but recently I wasn't sure. It's interesting the new table
gets carried over.
I have another question now!
If the tLogs are storing all the changes since the last backup, do they
get "emptied" next time you do a full backup?
As I understand it, you can choose to "truncate" the log as part of the
backup procedure, but I currently DON'T have this checked, so how do I
know my full backup really is "full" and will my tLogs keep growing
forever? All my backup settings are on the defaults. Can you advise the
correct backup settings to use for our simple setup?
(Point taken about doing full backup each day instead of each week)
Keith Kratochvil wrote:
> Q1 - When you apply the transaction log backups you will have all the
> modifications that have happened up to the point of the most recent
> transaction log that you applied.
> Q2 - Yes, the table that is created on Tuesday will be capured within
> Tuesday night's t-log backup. It will be "created" when you restore that
> log backup.
>
> If you have "lots" of user activity as you say you might want to issue
> transaction log backups more often than once per day.
>
Gerry Hickman (London UK)|||Gerry Hickman wrote:
> Hi Keith,
> Thanks, this is very helpful and this was how I originally understood it
> would work, but recently I wasn't sure. It's interesting the new table
> gets carried over.
> I have another question now!
> If the tLogs are storing all the changes since the last backup, do they
> get "emptied" next time you do a full backup?
> As I understand it, you can choose to "truncate" the log as part of the
> backup procedure, but I currently DON'T have this checked, so how do I
> know my full backup really is "full" and will my tLogs keep growing
> forever? All my backup settings are on the defaults. Can you advise the
> correct backup settings to use for our simple setup?
> (Point taken about doing full backup each day instead of each week)
> Keith Kratochvil wrote:
>> Q1 - When you apply the transaction log backups you will have all the
>> modifications that have happened up to the point of the most recent
>> transaction log that you applied.
>> Q2 - Yes, the table that is created on Tuesday will be capured within
>> Tuesday night's t-log backup. It will be "created" when you restore
>> that log backup.
>>
>> If you have "lots" of user activity as you say you might want to issue
>> transaction log backups more often than once per day.
>
Hi Gary
The FULL backup will not do anything to the logfiles. It's only a log
backup that will "touch" the logfile (Actually a FULL backup will take a
little part of the logfile, but that's only what it needs to be able to
perform a RESTORE).
When you do a log backup, it will mark the transactions that it has
backed up and the space can then be reused (TRUNCATE). Now the space can
be reused by new transactions, so your physical logfile doesn't need to
grow to contain the transactions.
Try to look up "Transaction Log Backups" in Books On Line. That chapter
gives a fairly good description of how it works.
Regards
Steen