Post reply

Best Practaces

  • March 16, 2004 at 6:33 am

    #162066

    I have one tape drive, DDS3, and alot of drive space.  Sofar, I have not been able to get a good backup stratagy in place and working.  I've been trying to use Veritas BackupEXEC 8, but I think I'm going to scrap that.  It's annoying me very much.
    I'm thinking of doing an hourly snapshot of transaction logs, then backing them up every day along with a full backup of the DB itself to magTape.  Is this a good idea?  I have a SP made to do the transaction logs, but it has some problems with the naming conventions I'm trying to use.
     
    Anyhew, any comments or advice is greatly appreciated.
     
    Tibby

  • March 16, 2004 at 6:44 am

    #498934

    Just wanted to add that Veritas is gone, it's annoyed me for the last time.  Also, my loss time is as low as possible, an hour won't kill us, but 2 may.  It's pretty critical stuff.  On the same note, if we lose 24 hours, it won't bring us to our knees, just cause some staff to redo the lost work.  No more than 24 hours can be lost though.  That's why I wasn't going to worry about the transact logs till nighttime when the full DB runs.
     
    Thanks again,
     
    Tibby

  • March 16, 2004 at 7:44 am

    #498950

    I have MS SQL Server 2000 and Veritas Backup Exec (both 8.6 and 9).

    My data is mission critical and my prefered method is to use SQL Server's BACKUP commands to backup the databases to disk and then use Veritas to COPY the backup file to tape.

    I do a full backup at midnight, a differential at noon, and transaction logs every hour except for when a full or differential are done.

    I do my backups to files that are identified by the type of backup:

    mydb_full.bak, mydb_diff.bak, master_full.bak, etc.

    I backup the system databases once a week.

    -SQLBill

  • March 16, 2004 at 8:12 am

    #498960

    That's the type of stratagy I'm looking at minus the mid-day diff.  The major issue I'm running into is setting up to do the the hourly transact log.  I'm setting up for every hour a new device is made, but I think that I will set it up for each day to be it's own device on disk, now I just need to figure out how to have SQL manage the files and keep the system from getting overloaded.
     
    Thanks again, Sueffel

  • March 16, 2004 at 9:22 am

    #498975

    You can always use a SQL Maintenance Plan to manage the log file retention.  I personally avoid maintenance plans whenever possible, but if I do need to use them, this is usually the way to go.

    You can also manage the log retention yourself by using a token when creating the file name.  You follow that with another job step that checks the dates and deletes any files that are past the time threshold (two weeks, etc).  I believe [TIME] is the token you want, so simply add that to the name of the file in the job step (e.g.,F:\backup\[TIME]_DBName.trn).  For a great discussion of tokens, check out Gert Drapers site (sqldev.net).

    Also, check the script library here -- there's some great ideas for handling backups.

    Hope that helps.

    J.

  • March 16, 2004 at 11:33 am

    #499012

    We have set of scripts which we run via SQL Agent jobs to do our database and log backups.  They are designed to use either native SQL Server backup or SQL LiteSpeed depending on a flag.

    Our basic strategy is a daily full backup of every database and log backups every 15 minutes for databases in Full or Bulk-logged recovery models.  Our log backups append to a single backup file and do an Init when it detects a full backup has run since the last log backup.

    The scripts we use were published with the book SQL Server 2000 High Availability by Allan Hirt.  I could also get the updated scripts to you if you'd like, just send me a PM.

    Regards,

    Steve


    Regards,
    Steve

    Meddle not in the affairs of dragons, for you are crunchy and taste good with ketchup.

  • March 16, 2004 at 11:47 am

    #499014

    I agree with Steve.  Save yourself the hassle of managing a bunch of separate backup files and just append all the log backups for a day to the backup device the contains the full backup from the previous night.  If you copy the backup file to tape each night before the full backup runs, you'll only need one device.  You have the added advantage of having to use only one backup file for a specific day when restoring to a point in time.

    Greg

    Greg

  • March 16, 2004 at 1:40 pm

    #499029

    I agrre with Steve as well, it seems I'm thinking of things the wrong way.  The major Q is do I use Windows Backup to copy the whole disk device to tape, or can SQL do this?  I'll PM Steve and ask if he can send the scripts, that should give me a good idea of what I'm looking at and what I need to do.
     
    Thank you soo very much everyone, backups has never been my strongest suit, I've depended on keeping multiple copies and multiple servers.
     
    Tibby

  • March 17, 2004 at 6:07 am

    #499098

    We have Backup Exec 9.0 but for my mission critical transactional databases I use SQL Server backup tool to backup to a different server (in this case it is my file server). That way if I have a database server failure I can restorse my backups to my warm standby server, change the name, reboot, and we are up and running. Also if you needed to do a backup to the database server, it is faster since the file is always accessable and you don't have to chase down a tape. I let the sys admin keep using the Backup Exec to back all the databases in SQL (only once a day) and these are taken off site nightly. If you have a database that changes alot like mine, you can't use a tape drive very effectively and tape has a bigger failure rate than disk. I do a once a night backup, with diff's at the top of the hour, and transactions every 20 minutes.

     

    Matt

  • March 17, 2004 at 3:09 pm

    #499224

    Well to be a fly in the ointment, personally, I disagree with Steve. Running the backups to a single backup device you run the risk of losing all your backups if that file becomes corrupted.

    We do nightly backups with tran log backups every 15 mins. All of these backups go to seperate files, not backup devices, on a seperate server. This server is backed up to a tape silo using Backup Exec throughout the day.

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply