Backups

Hi Guys,

I'm pretty new to SQL Server. I have a ? regarding backups.

I have a database which I'm backing up everyday using BACKUP DATABASE db TO db_bak WITH INIT. This job runs everyday successfully, but I'm having an evergrowing transaction log file. I presumed a full database backup will apply transaction logs and will truncate the log files. Isnt it so, or am i missing some concepts? This database can have a data loss of 1 day which is covered by my full database backup job. I dont need a transaction log backup. But to reduce log file size should I do a log backup?

-Sunil.Depends...(love that answer)

What's your recovery model? Simple would do what you're thinking...

I have a feeling it's FULL...in which case you need to dump the tranny too...

Look at the maint plan wizard to help you out

Also you need to determine what type of strategy you need to employ...

Basically, how much time/data can you afford to lose...|||Thanks Brett,

My recovery model is FULL and i prefer to keep it that way. So, i reckon I should set transaction log backups aswell. I have set database backup to run every 6PM, if i create another job to backup transaction logs at 7PM, will it do? Also, since my database is big (It is MSProject database) database backup is done everyday in overwrite mode to avoid appending 200MB everyday to the backup file. Could transaction log backup be done in overwrite mode aswell or is it necessary to be appended every day? If there is a recovery scenario, will it all add well?

I'm still wondering why isnt transaction log truncated after a successful full backup. Why do we need transaction logs if the database itself is fully backed up.

-Sunil.|||Originally posted by sunilthomas98
I'm still wondering why isnt transaction log truncated after a successful full backup. Why do we need transaction logs if the database itself is fully backed up.
-Sunil.

Because it is a point in time backup...logs are usually dumped periodically to recover data throughout the daya (I do mine every 10 minutes)

Why not first go through the db maint wizard...it'll show you all the things you can do, without having to code them in T-SQL...it'll even schedule the jobs...

Then by a book...one of my favorites is SQL Server 911 by Brian Knight..good book

Here's a real story...

HR "dba" (the quotes are on purpose) had the type of scenario you mention..

It was time to enter in all the work for the bonuses...which got done...

Now scrub boy messed something up...we think he had a glitch a decided to recover the db from last nights dump...effectivley wiping out all the work the clerks had enetered...soooooo no bonuses...right?

Wrong...the vp called ALL of the clerks in plus IT staff to re-enter the data...so at about 4:00 am it was done...and the file went out the next day...

He's not with us anymore...|||Thanks Brett, I'll do that not to miss my next bonus (if it comes)