backups - complete, differential and transaction log

Hi,
I am new to database administration and I was looking at the backups setup
on a database-
Full backup - every week, overwrite existing media
Differential backup - every night, append to media
Transaction log backup - every hour, append to media
The size of the differential backup file has grown to 40 GB while the actual
database full backup is just 1 GB. This led me to wonder why do we need to
use the setting "Append to media" for the Differential Backup. It should be
"Overwrite existing media" since I am using a disk to write the backups?
Am I correct? What's the correct setting for transaction log backups? Thanks.For DIFFERENTIAL backups: When you have to restore from backups, you will
need to restore the last FULL backup, and then restore the last DIFFERENTIAL
backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
since the last FULL backup. (And then, of course, the remaining TLOG backups
made since the last DIFFERENTIAL.)
Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
during the stressed restore
It is ok to have only the last DIFFERENTIAL.
TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
> Hi,
> I am new to database administration and I was looking at the backups setup
> on a database-
> Full backup - every week, overwrite existing media
> Differential backup - every night, append to media
> Transaction log backup - every hour, append to media
> The size of the differential backup file has grown to 40 GB while the
> actual
> database full backup is just 1 GB. This led me to wonder why do we need to
> use the setting "Append to media" for the Differential Backup. It should
> be
> "Overwrite existing media" since I am using a disk to write the backups?
> Am I correct? What's the correct setting for transaction log backups?
> Thanks.|||Thanks, Arnie for the quick reply. When you say that "TLOG backups should be
'Append to Media' DO NOT overwrite", we just need to keep the TLOG backups
since the last DIFFERENTIAL BACKUP, we don't need any TLOG backup before that.
Am I correct?
"Arnie Rowland" wrote:
> For DIFFERENTIAL backups: When you have to restore from backups, you will
> need to restore the last FULL backup, and then restore the last DIFFERENTIAL
> backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
> since the last FULL backup. (And then, of course, the remaining TLOG backups
> made since the last DIFFERENTIAL.)
> Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
> during the stressed restore
> It is ok to have only the last DIFFERENTIAL.
> TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
> FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
> > Hi,
> >
> > I am new to database administration and I was looking at the backups setup
> > on a database-
> > Full backup - every week, overwrite existing media
> > Differential backup - every night, append to media
> > Transaction log backup - every hour, append to media
> >
> > The size of the differential backup file has grown to 40 GB while the
> > actual
> > database full backup is just 1 GB. This led me to wonder why do we need to
> > use the setting "Append to media" for the Differential Backup. It should
> > be
> > "Overwrite existing media" since I am using a disk to write the backups?
> >
> > Am I correct? What's the correct setting for transaction log backups?
> > Thanks.
>
>|||What you need to keep only you can decide. When you do a restore, you use:
Most recent db backup
Most recent diff backup
All subsequent log backups after the diff backup.
But:
Say you had a database corruption introduced between the last db backup and the most recent diff
backup. And say that the corruption is not still present if you restore according to above scheme.
You would not want to restore the most recent db backup, and all subsequent log backup (so the
corruption is not likely to be re-introduced).
Or say that you need a state of the database as it were at tuesday 14:34. If you did db backup sun,
diff mon, diff, tue, diff wed, diff thu, you would now want to restore db, diff tue night, and log
backups until that point in time.
So you do need to carefully consider your possible restore scenarios, before you can say what you
need.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:3E7C4E57-2AAB-47C1-A365-99AA537DC2AD@.microsoft.com...
> Thanks, Arnie for the quick reply. When you say that "TLOG backups should be
> 'Append to Media' DO NOT overwrite", we just need to keep the TLOG backups
> since the last DIFFERENTIAL BACKUP, we don't need any TLOG backup before that.
> Am I correct?
> "Arnie Rowland" wrote:
>> For DIFFERENTIAL backups: When you have to restore from backups, you will
>> need to restore the last FULL backup, and then restore the last DIFFERENTIAL
>> backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
>> since the last FULL backup. (And then, of course, the remaining TLOG backups
>> made since the last DIFFERENTIAL.)
>> Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
>> during the stressed restore
>> It is ok to have only the last DIFFERENTIAL.
>> TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
>> FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to the
>> top yourself.
>> - H. Norman Schwarzkopf
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
>> > Hi,
>> >
>> > I am new to database administration and I was looking at the backups setup
>> > on a database-
>> > Full backup - every week, overwrite existing media
>> > Differential backup - every night, append to media
>> > Transaction log backup - every hour, append to media
>> >
>> > The size of the differential backup file has grown to 40 GB while the
>> > actual
>> > database full backup is just 1 GB. This led me to wonder why do we need to
>> > use the setting "Append to media" for the Differential Backup. It should
>> > be
>> > "Overwrite existing media" since I am using a disk to write the backups?
>> >
>> > Am I correct? What's the correct setting for transaction log backups?
>> > Thanks.
>>

backups

I have some backups scheduled on my sqlserver.most of them are differentail backups.But among them some have options 'Append to media' and some of them are 'Overwrite existing media '. But i dont know which of them falls into which category.How can i find that and can i cahnge 'Append to media' to 'Overwrite existing media '?If yes how can i do that?Does it effect my db if i change it?
INfact which one is the best option ('Append to media','Overwrite existing media ') among these two?
Thanks.you need to identify your app requirements, retention resources availabilities, disaster recovery processes (if exist, else - develop them based on business needs), and then tailor your backup strategy to the above. looks like you inherited the box(-s) from someone else and nothing is documented.|||yep, nothing is documented abt the existing backups,
How do i know the info?

Backup with log truncation

I have a database aaa and i'm taking a full backup daily at 12 Am(I'm overwriting to the existing media) and at the sama time I'm also running the script given below to truncate the transaction logs.

BACKUP LOG [aaa]
WITH TRUNCATE_ONLY

Can i add this script in my full bcak? Please help me in this issue?What's you're recovery model set to?

How are you taking the dump?|||How are you taking the dump?

Brett - This is a little personal - for me I enjoy reading the sports page.