Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Aug 2006
    Posts
    28

    Unanswered: Help me understand/engineer a backup strategy.

    I tried searching, but it appears the search is still broken.

    Some of you know my other posts.. pleading for help with a problem (that's really sort of fixed itself/not remanifested itself).

    Upon this whole ordeal I decided to re-evaluate my nonchelant backup strategy. Which currently is:

    Backup our DB daily w/ transaction logs. (overwrite the backup daily on the tape).
    Backup our DB weekly w/ transaction logs.

    I rotate the tape weekly, on Monday morning. So there is a weekly backup and the last backup from monday morning on each tape.

    Along with that I have some optimizations and shrinks I run.


    Reading through the SQL BOL. It seems my strategy is ok, however if we lost the db at the end of the day, we'd lose a full days worth of work.

    So. Here in lay the questions.

    The BOL states to possibly back up twice a week, with differential daily backups and 4 hour transaction logs (example).

    So, I sorta get that..

    I make a device, "COMPANY.BAK", which writes to my external 'tape' unit.

    I make a job to back up the complete DB on sunday. to "COMPANY.BAK". (Not sure if I tell it to truncate the transaction log?).
    I make another job to make nightly differential backups to "COMPANY.BAK" with the 'append to media' switch on.
    I make yet ANOTHER job to make a transaction log backup. Here's what I don't get. If I set this thing to run every 4 hours, since it's not a differential backup.. rather incremental.. doesn't it just overwrite itself each time? It doesn't append itself to the backup right? So how much data could be missing if the log is overwriting itself rather than appending to itself? Also, if I turn on truncate log on backup, aren't I missing possibly critical transactions from the log for a sucessful restore?

    I guess I'm a bit befuddled here.
    Last edited by Mindflux; 08-16-06 at 19:32.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How big are your backup files?
    My default strategy is a nightly full backup and hourly transaction log dumps between 6:00 am and 8:00 pm (working hours). All backups and log dumps are to disk, but the new files are immediately copied to a network location.
    That way, I have point in time recovery for database corruption using the local files, and hourly recovery from the network file copies should the server accidently get smashed by a loose gorrilla or fall into the vat of KY Jelly (perhaps I'm giving away too much about my workplace...).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2006
    Posts
    28
    Ok. but how do you set the log file to backup every "X" hours? When I set it up, it seems like it'll overwrite the previous log backup?

    My DB is in the 7GB range, right now my logfile is 1GB (dunno how to trim it, necessarily).

    How do I keep a sane size log but not run into problems with restoring a broken DB (If this should ever happen?)


    I'm lost, trying to be a dba when in fact I'm not.

    Don't you also need to do database differentials? If you have a backup and 6AM, but crash at 7:45 am... and have no db diffs, dont you lose 1 3/4 hours of work? or can you roll that back with the logs?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, you do not need to do differentials. You can restore a database to any point in time using only the most recent full backup, the log dumps that have occured since then, and whatever can be recovered from the current transaction log.
    Using the maintenance plan wizard you can create a job that runs the sqlmaint utility. This utility will create database backups and log backups, each with a unique name so that they are never overwritten, and can be set to delete files older than X days so that your disk doesn't fill up. My backup job command looks like this:
    [EXECUTE xp_sqlmaint N'-PlanName AllDatabases -Rpt e:\SQLBackup\BackupDatabases.rpt -WriteHistory -BkUpDB e:\SQLBackup -BkUpMedia DISK -DelBkUps 5Days -CrBkSubDir'[/code]
    You absolutely MUST read (or re-read) the BOL section on database and log backups so that you understand the difference between the two, and know what a differential backup is for. Then read the section on xp_sqlmaint. Then come back to the forum for any additional help.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2006
    Posts
    28
    Ok.

    I'm grasping this a bit better. Thank you.

    What I guess I *STILL* don't get is the role of the transaction log. Say I back up at 6, crash at 7:45. If I have logs every 2 hours, I've lost everything from 6 - 7:45, right?

    Ok, so, say I had logs in 15 minute intervals. So I could restore up to 7:30. But how does a "LOG" (to me a log is just a log of what's happened, like when I fish through my apache logs on my FreeBSD server). So how does a "LOG", restore data that wasn't in the database at the backup at 6am?

    Does the LOG basically log every insert, delete etc query, with the data being changed?

    So say my users add a new client to the DB at 7.... when does it actually get written to the MDF, as opposed to sitting around in the LDF? At the next DB backup? At the next Transaction Log Backup?

    When is it safe to get rid of logs? I'm assuming after a full or differential backup is done? Since everything that's been 'committed' is now backed up to a .BAK?

    FWIW I have been reading the BOL administrators pocket consultant. Is this not the right one to use for backup references?
    Last edited by Mindflux; 08-17-06 at 11:02.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by Mindflux
    Does the LOG basically log every insert, delete etc query, with the data being changed?
    Yep. Every change made to a database is first written into the log. Only after the write to the log has been reported successful by the O/S will SQL Server (Or any other RDBMS worth getting) will write the change to the data files.

    When to get rid of the logs is up to you. You >could< keep them for years, but they will only be as good as the full backup(s) before them. It all depends on what your users are willing to live with as far as data loss. Point in time recovery is also useful when the DBA (Database Arsonist) comes along and wipes out a table or two. He will usually not let you know about this, until he has tried to cover up his mistake with a few tries that usually only make things worse. This could take a couple hours depending on how many users there are, how many tricks the DBA knows, and how long it takes him to finally own up to making a mistake.

    Since you only have a 1GB log, it probably does not take up a lot of space on the tape. Too many backups is a better ptoblem than too few, in my opinionated opinion ;-).

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by MCrowley
    Point in time recovery is also useful when the DBA (Database Arsonist) comes along and wipes out a table or two. He will usually not let you know about this, until he has tried to cover up his mistake with a few tries that usually only make things worse. This could take a couple hours depending on how many users there are, how many tricks the DBA knows, and how long it takes him to finally own up to making a mistake.
    Did we used to work together?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Aug 2006
    Posts
    28
    Alright, thanks again folks.

    This is my current strategy.

    1) Backup db nightly at midnight to tape
    2) Backup tlogs from 7a-7p in 1 hr increments (I dont need to backup the t-log right after the nightly db backup, right?)
    3) Backup the system db's weekly (midnight on Sunday)
    4) Run a reoganize optimization weekly (Sunday morning) on our primary db
    5) Run a DB shrink on Sunday morning (weekly) on our primary db.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Looks fine. I generally backup the system dbs nightly as well. Not necessary, but it costs practically nothing and I believe the simplicity increases reliability.
    Now, have you ever TRIED restoring a database?
    Do it (not on your production system).
    Restore a backup.
    Restore a backup and the first three or four transaction logs.
    Try a point-in-time restore.
    The time to learn how to restore databases is BEFORE you have to restore a database, and no un-tested backup strategy is a safe backup strategy.
    Like it or not, you are the DBA for your group, and you are going to have to set aside a couple hours each week for admin.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I don't see testing the restore part of your strategy. Make sure you have confidence that you can recover your databases by implementing disaster recovery drills ... or you might find that your tape has unrecoverable errors that prevent restores.

    Lots of folks prefer backup first to disk, then save that file to either tape or another disk storage device. Also to disk and hold for a couple of days helps when you get the inevitable call "... I screwed up a table and need help restoring x number of rows or the business will go down the tubes in the next y hours"

    And it will happen ... remember ... Murphy was an optimist! That's my creedo and guiding light!

    -- This is all just a Figment of my Imagination --

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would take out the shrink step, unless you are really hurting for disk space. Growing database files is a somewhat expensive in disk activity, so it should be avoided if possible.

    The only other thing to mention is that backups of a database block each other, so if you try to run a full and a log backup at the same time, one of the backups will have to wait for the other to finish. Probably not a problem in your case, but good to know.

    Quote Originally Posted by Blindman
    Did we used to work together?
    Nope. But the same "DBA" has probably worked for both our companies.

  12. #12
    Join Date
    Aug 2006
    Posts
    28
    Ok. Thanks again.

    I'll remove the shrink step, although the db seems rather swollen at this time (12gb, as opposed to 7 when it's shrunken).

    As far as testing a restore. I have not. This is the only SQL server in the whole building. Does that mean I cannot test it?

    Can I not try and restore the DB to another db name? such as foo-restore-test or something?

    I was originally backing up to disk, and then running a nightly NTBACKUP job to move those to my tape(redundancy, yah?) but I found myself sort of hunting and pecking for time in the morning for the db to backup, and then yet another job to run (after I am SURE the backup was complete (usually hours later) .

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Backing up to disk, and then letting the filesystem backup collect the file is actually pretty common. A lot of DBA (The real sort) like to have the latest copy of the database handy, so they don't have to go sorting through tapes to find the one with the latest backup. Some places even send their tapes off site for storage, in case the whole building burns down.

    You can restore the database with a new name, but you will also need to move the datafiles, or you will get "File Exists" errors when you try the restore. Hunt around in the backup/restore software for anything that looks like "move datafile".

  14. #14
    Join Date
    Aug 2006
    Posts
    28
    Alright. I'll look into backing up to disk and try and find an easy way to backup to my tape setup too. I'd like to the tape to get the hourly tlog backups too. Just in case, ya never know, right?

    The NTBACKUP way just seems a bit... rudamentary. Since it'll put everything in a large backup archive, which I'd have to restore (ie let the system extract the data from the archive), and then run a sql restore.

    I'd rather just have the tape mirror my disk backup on some sort of schedule. I'm not aware of a way to do that in SBS2k3 Premium without using NTBACKUP.

  15. #15
    Join Date
    Aug 2006
    Posts
    28
    Now that I think of it, wouldn't there be a way to have the SQL Maintenance manager duplicate the data? Or would that do the actual backing up part twice?

    I see I can do it with the non wizarded backup, I can tell it to go to LOCAL and TAPE (just make two backup 'devices'). It seems to work... I tested it on the master db.

    My only problem with that is I see no logging option there or way to tell it how many days to keep.

Posting Permissions

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