Post reply

Backup Debate...

  • June 26, 2003 at 6:49 am

    #105771

    We are trying to figure out the best way to backup our SQl 2k Databases.

    First: Our primary database is about 90gigs, and then we have another 10 gigs of minor databases.

    We have a 6 hour down time every night, midnight to 6am to do anything we want, so time isn't an issue.

    Our Backup is an IBM LTO 3581 Tape Drive.

    First way:

    Midnight: Shut Down SQL

    Arvserve with open file agent the entire sql drive, the logs, etc.

    3am bring SQL back up online and purge the logs.

    2:

    Run a SQl backup of the databases and logs to a spare drives.

    Backup the spare drives with the LTO

    There is concern that someone could possible do something between the log backup and the purge, if the system isn't offline

    Any hints appreciated..

    Brad

    Edited by - bly on 06/26/2003 06:51:10 AM

  • June 26, 2003 at 7:29 am

    #463237

    #2. No way I'd want to take my service down nightly, and no reason to as far as Im concerned. Definitely a good idea to back up to disk and from there to tape.

    Andy

    http://qa.sqlservercentral.com/columnists/awarren/

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • June 27, 2003 at 6:59 am

    #463445

    Hi,

    I have been using the second method for a few years for the following reasons:

    1.Our tape drive is not big enough to hold the DB backup created by ArcServe; it (our version, at least) does no compression using

    their SQL backup engine. When I backup the .BAK file to tape, it shrinks down quite a bit.

    2.Occasionally the tape backup fails for some reason or other. When I get in in the morning it would leave me with a problem, since I can't take the system away from my users. Using the disk backup, I can restart the tape job any time and have no affect on my users.

    3.The disk backup runs very much faster than the tape job. Therefore, the chance of changes during that time are rather minimal.

    4.Doing the backup to disk gives me an extra copy of the backup. I would certainly use the disk copy before trying to restore from the tape!

  • June 27, 2003 at 7:07 am

    #463453

    Am in agreement with the previous two. Backup to disks first. Backup those backups to tape.

    If you use transaction logs, one quirk I have found is that you need to backup the transaction log prior to backing up the database. Not doing so made subsequent log restores after the db restore(in test) iffy. By doing it before the start of the backup, I have never had any problems restoring the logs to a restored db.

  • June 27, 2003 at 8:04 am

    #463472

    You might even want to consider performing a BCP out job on the tables in the database. BCP is very fast and the output is data only. Of course make sure you have a rebuild script to restore the database structure.

  • June 27, 2003 at 9:24 am

    #463488

    Yesterday,

    All those backups seemed a waste of pay.

    Now my database has gone away.

    Oh I believe in yesterday.

    Suddenly,

    There's not half the files there used to be,

    And there's a milestone

    hanging over me

    The system crashed so suddenly.

    I pushed something wrong

    What it was I could not say.

    Now all my data's gone

    and I long for yesterday-ay-ay-ay

    Yesterday,

    The need for back-ups seemed so far away.

    I knew my data was all here to stay,

    Now I believe in yesterday.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 27, 2003 at 10:17 am

    #463501

    We have a db that was 50GB of data. Went to Litespeed, shrank to 8GB. Time went from over an hour to under. I'd get Litespeed and run it to disk, then to tape.

    Steve Jones

    sjones@sqlservercentral.com

    http://qa.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • June 27, 2003 at 10:39 am

    #463509

    We use a variation of #2. We backup to disk, then copy to disk on another server. Then these are both backed up to tape. The reason we copy to the second server is our tapes are off-site and if the original server dies, we can pull the backups off the second server we copied them to!!

  • June 27, 2003 at 11:13 am

    #463513

    We have a 60+GB database with near 24x7 availability. We have a very short 5 minute window between 9:25 and 9:30 pm where we want to have a "snapshot" backup of the database that represents the start of nightly batch processing. We accomplish that with the following approach:

    At 8:00 :

    Backup Log <database> WITH TRUNCATE_ONLY

    Backup Database <datebase> TO <datadisk> WITH INIT

    Backup Log <database> to <logdisk> WITH INIT

    Then at 9:25:

    Backup Log <database> to <logdisk> with NOINIT

    The log dump finishes in just a matter of seconds.

  • July 1, 2003 at 4:20 am

    #463744

    I want to know where the script that take the Db and copy it to another partition ??

    and is it possible to copy DB get out of Database maintainace planner (that output data with specific names formats, including time and date)??

    Alamir Mohamed

    Alamir_mohamed@yahoo.com


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • July 8, 2003 at 11:57 pm

    #464615

    We use SQL Litespeed for all our SQL servers, then we back these backups to disk. It's cheap and heaps faster than native SQL backups. It shrunk our 30 gig database down to 3 gig and it's encrypted. Not to mention it's another great Australian product! 🙂

  • July 9, 2003 at 7:42 am

    #464686

    My vote is first goto disk then tape. tape is slow and you'll never make your 6 hour window.

Viewing 12 posts - 1 through 11 (of 11 total)

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