Results 1 to 5 of 5

Thread: Backups

  1. #1
    Join Date
    Aug 2003
    Posts
    41

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

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Posts
    41
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2003
    Posts
    41
    Thanks Brett, I'll do that not to miss my next bonus (if it comes)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •