SQL backups - Veritas Backup Exec

This is a discussion on SQL backups - Veritas Backup Exec ; I thought I understood all of this, however the more I read, the more I'm confused. I'm backing up a SQL server, with agent, using the "full backup" method. This is all fine, though I have one database that keeps ...

+ Reply to Thread
Results 1 to 4 of 4

Thread: SQL backups

  1. SQL backups


    I thought I understood all of this, however the more I read, the more I'm
    confused.

    I'm backing up a SQL server, with agent, using the "full backup" method.
    This is all fine, though I have one database that keeps changing from "full
    mode" to simple mode. So that brings up the first question. Do I want the
    sql DB's in simple or full?

    Now, I would like to do backups during the daytime to a NAS unit incase development
    guys screw up the database. What type of backup do I really want to run?
    I'm a little confused wether I just run a database backup, and or if I need
    to run a separate backup for log files, or ... ?

    Finally, If I need the databases in "full mode" any ideas on why this one
    database is switching to simple? I've checked all the sql jobs and can't
    find anything that is changing them.

  2. Re: SQL backups

    Not sure why it would switch from Full to simple recovery mode.
    Full recovery is only useful if you need to restore your database to a
    specific point in time. I have a db that we need snapshots taken every 2
    hours. This allows me to restore not only to restore to a specific day, but
    to within 2 hours of the problem. My DB is 90 GB but my transaction logs are
    only 2 GB. Its much quicker to take 12 x 2 GB transaction log backups
    throughout the day than it is to do 12 x 90 GB Full backups. In my case, my
    db MUST be in full recovery mode. Note however, that since this DB is in
    full mode, I MUST perform transaction log backups or they will never commit
    their transactions and purge themselves. If you are using BE only (and not
    sql enterprise manager's maintenance plans) a full backup job in BE is not
    enough, you must also schedule a transaction log backup (preferably at least
    1 per day; preferably, just before your full backup.)
    I have another 60 GB db that is just archive data. I only do 1 backup a day
    on it. I keep this one in simple recovery mode. No need for a separate
    transaction log backup (in either BE or sql ent mgr)

    "Don B" wrote in message news:41da1511@ROSASTDMZ05....
    >
    > I thought I understood all of this, however the more I read, the more I'm
    > confused.
    >
    > I'm backing up a SQL server, with agent, using the "full backup" method.
    > This is all fine, though I have one database that keeps changing from

    "full
    > mode" to simple mode. So that brings up the first question. Do I want the
    > sql DB's in simple or full?
    >
    > Now, I would like to do backups during the daytime to a NAS unit incase

    development
    > guys screw up the database. What type of backup do I really want to run?
    > I'm a little confused wether I just run a database backup, and or if I

    need
    > to run a separate backup for log files, or ... ?
    >
    > Finally, If I need the databases in "full mode" any ideas on why this one
    > database is switching to simple? I've checked all the sql jobs and can't
    > find anything that is changing them.




  3. Re: SQL backups

    Hum, I was under the assumption that doing a "full backup" my log files
    would trunicate and take care of everything. Am I not understanding
    something?

    I have 12 databases (at least), though the database sizes are less than 10
    gig apiece.

    I would like to be able to restore either every hour or two in a disaster.
    How would you do this if it was your network? I do have a NAS unit to do
    disk backups to.


    "Chris Knapp" wrote in message news:41daeaf7@ROSASTDMZ05....
    > Not sure why it would switch from Full to simple recovery mode.
    > Full recovery is only useful if you need to restore your database to a
    > specific point in time. I have a db that we need snapshots taken every 2
    > hours. This allows me to restore not only to restore to a specific day,
    > but
    > to within 2 hours of the problem. My DB is 90 GB but my transaction logs
    > are
    > only 2 GB. Its much quicker to take 12 x 2 GB transaction log backups
    > throughout the day than it is to do 12 x 90 GB Full backups. In my case,
    > my
    > db MUST be in full recovery mode. Note however, that since this DB is in
    > full mode, I MUST perform transaction log backups or they will never
    > commit
    > their transactions and purge themselves. If you are using BE only (and not
    > sql enterprise manager's maintenance plans) a full backup job in BE is not
    > enough, you must also schedule a transaction log backup (preferably at
    > least
    > 1 per day; preferably, just before your full backup.)
    > I have another 60 GB db that is just archive data. I only do 1 backup a
    > day
    > on it. I keep this one in simple recovery mode. No need for a separate
    > transaction log backup (in either BE or sql ent mgr)
    >
    > "Don B" wrote in message news:41da1511@ROSASTDMZ05....
    >>
    >> I thought I understood all of this, however the more I read, the more I'm
    >> confused.
    >>
    >> I'm backing up a SQL server, with agent, using the "full backup" method.
    >> This is all fine, though I have one database that keeps changing from

    > "full
    >> mode" to simple mode. So that brings up the first question. Do I want
    >> the
    >> sql DB's in simple or full?
    >>
    >> Now, I would like to do backups during the daytime to a NAS unit incase

    > development
    >> guys screw up the database. What type of backup do I really want to run?
    >> I'm a little confused wether I just run a database backup, and or if I

    > need
    >> to run a separate backup for log files, or ... ?
    >>
    >> Finally, If I need the databases in "full mode" any ideas on why this
    >> one
    >> database is switching to simple? I've checked all the sql jobs and can't
    >> find anything that is changing them.

    >
    >




  4. Re: SQL backups

    At least in BE 9.x, a full SQL backup on a full recovery mode db will NOT
    backup the transaction logs.

    If it were me ;-) lol.
    I really can't answer without knowing your specifics. Mind you, I'm no
    backup expert. The below comments are my opinions taken from making (and
    learning from) mistakes along the way.

    There are a bunch of things you have to know first off:
    1. Your "actual" network speed. conduct some tests to see how fast you can
    backup/restore data across the LAN. Also test how fast your disk subsystems
    are at reading & writing. Where are your bottle necks?
    2. Your backup capacity: how much offline storage do you have access to at
    any give time? (I have a 16 tape autoloader with 6.4TB (compressed)
    capacity, so I can run a complete weeks worth of jobs before swapping
    tapes.) We all know that having to switch tapes mid-job sucks; especially
    over holiday weekends when you've have too many "libations."
    3. Your Service Level Agreements [SLA] Basically, how much time you
    "promised" the boss you would need to recover from their screwups.
    Hopefully, you under-promised and can set yourself up to over-deliver.

    Once you know your assets & liabilities, you can start to plan.
    Backup-to-disk's biggest strength is its near-line capability: you don't
    have to load any tapes (or worse, get them out of storage/offsite) If you
    expect a need to recover within 20 minutes, then having your critical
    backups on disk is the way to go.
    Tape's biggest strengths are its low cost (compared to disk) and its
    portability. Long term & relaxed SLA backups go to tape. For smaller
    companies, everything goes to tape.

    In your situation, you would need to setup your DB's in full recovery mode.
    Then you setup 1 BE job to do a Full backup daily. Then, you create another
    job to run a transaction log backup every 2 hours. You have to make sure
    your jobs don't share tapes or get overwritten or anything else that will
    screw up your ability to restore. This usually means having multiple media
    sets, multiple tapes, and a good idea how BE will append/overwrite in your
    setup. Your daily backup job can be setup to alternate tapes (in case it
    goes bad during an overwrite.) and your hourly job can be set to append
    (given enough tape space.) You can even setup a different job for each day
    of the week if you have enough tapes and the ability/motivation to swap them
    out. An autoloader really helps here. . .

    Hopefully this gives you some ideas. . .

    "Don Bentz" wrote in message news:41db0a08@ROSASTDMZ05....
    > Hum, I was under the assumption that doing a "full backup" my log files
    > would trunicate and take care of everything. Am I not understanding
    > something?
    >
    > I have 12 databases (at least), though the database sizes are less than 10
    > gig apiece.
    >
    > I would like to be able to restore either every hour or two in a disaster.
    > How would you do this if it was your network? I do have a NAS unit to do
    > disk backups to.
    >
    >
    > "Chris Knapp" wrote in message

    news:41daeaf7@ROSASTDMZ05....
    > > Not sure why it would switch from Full to simple recovery mode.
    > > Full recovery is only useful if you need to restore your database to a
    > > specific point in time. I have a db that we need snapshots taken every 2
    > > hours. This allows me to restore not only to restore to a specific day,
    > > but
    > > to within 2 hours of the problem. My DB is 90 GB but my transaction logs
    > > are
    > > only 2 GB. Its much quicker to take 12 x 2 GB transaction log backups
    > > throughout the day than it is to do 12 x 90 GB Full backups. In my case,
    > > my
    > > db MUST be in full recovery mode. Note however, that since this DB is in
    > > full mode, I MUST perform transaction log backups or they will never
    > > commit
    > > their transactions and purge themselves. If you are using BE only (and

    not
    > > sql enterprise manager's maintenance plans) a full backup job in BE is

    not
    > > enough, you must also schedule a transaction log backup (preferably at
    > > least
    > > 1 per day; preferably, just before your full backup.)
    > > I have another 60 GB db that is just archive data. I only do 1 backup a
    > > day
    > > on it. I keep this one in simple recovery mode. No need for a separate
    > > transaction log backup (in either BE or sql ent mgr)
    > >
    > > "Don B" wrote in message news:41da1511@ROSASTDMZ05....
    > >>
    > >> I thought I understood all of this, however the more I read, the more

    I'm
    > >> confused.
    > >>
    > >> I'm backing up a SQL server, with agent, using the "full backup"

    method.
    > >> This is all fine, though I have one database that keeps changing from

    > > "full
    > >> mode" to simple mode. So that brings up the first question. Do I want
    > >> the
    > >> sql DB's in simple or full?
    > >>
    > >> Now, I would like to do backups during the daytime to a NAS unit incase

    > > development
    > >> guys screw up the database. What type of backup do I really want to

    run?
    > >> I'm a little confused wether I just run a database backup, and or if I

    > > need
    > >> to run a separate backup for log files, or ... ?
    > >>
    > >> Finally, If I need the databases in "full mode" any ideas on why this
    > >> one
    > >> database is switching to simple? I've checked all the sql jobs and

    can't
    > >> find anything that is changing them.

    > >
    > >

    >
    >




+ Reply to Thread