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

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 Hickman
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.
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:
>
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

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:
>
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

Backup User - Server Role?

I wish to create a user that can backup any or all databases in our SQL
Server 2000 Instance. I thought there would be a server role for this
function, however I can only find that after I grant access of a
database to the user, then I can choose ds_backupoperator.
I want to create a user that will have the ability to backup all the
databases. I dont wish to have to come back to the server after a new
table is created and add the backup user to that table.
I want SA w/o the full privilage...am I crazy?
Any Suggestions?
TIA
Rob
Backgroup: We currently have about 10 SQL servers, and adding more in
the future. I am using SQLBackup from Idera along with HP Surestore
Tape library (60 slots,2- DLT8000 drives with 40/80 GB capacity) with
ArcServe from Computer Associates. I want to have this automated to
backup to file then tape, regardless of what databases get created.rcamarda (rcamarda@.cablespeed.com) writes:
> I wish to create a user that can backup any or all databases in our SQL
> Server 2000 Instance. I thought there would be a server role for this
> function, however I can only find that after I grant access of a
> database to the user, then I can choose ds_backupoperator.
> I want to create a user that will have the ability to backup all the
> databases. I dont wish to have to come back to the server after a new
> table is created and add the backup user to that table.
> I want SA w/o the full privilage...am I crazy?

I guess the reason you need to be sysadmin to backup a database, is that
BACKUP DATABASE gives you access to the data in the table. Once you have
a backup, and can restore anywhere you like - and with any privilege.

I believe there is a way to add a user to db_backupoperator to all
future database: add the user to this role in model. I have not tried
this, though.

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

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