Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Unanswered: Backup without truncating logs?

    I've got a Sql server 2000 box that currently backs up to tape in full every night. I also want to back the box up off site but not in full (as 30GB is a little too much to transfer every evening).

    So my plan was to do a full backup to tape at 7pm then a differential at 8pm (to transfer off site).

    The problem I am having is that after my differential has been done the logs get truncated so if I want to replay them for any reason I need to get that differential back to site.

    Anyone have any suggestions please?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    file compression and secure ftp?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    and encryption of course.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Or do as the British government do - just stick it on DVD and put it in the postbox
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    For an off-site copy of the database, you can look at some of the following options. All have their pluses and minuses, so Books Online will be your friend for the rest of the week:

    Log Shipping
    Replication
    3rd party replication tools (We use DoubleTake here).
    Database Mirrorinig (SQL 2005 only)

  6. #6
    Join Date
    Dec 2007
    Posts
    2
    Thanks for the replies. Sorry, I should have been more clear:

    The security or the transport isn't the issue. We're using ipsec and just transferring to a remote file share. The issue is that every night there are two backups: a full backup to tape (with veritas) then a differential (using sql server itself) to the file system which we transfer off site.

    I'm worried that the differential will zero the logs thus making our off site differential essential for a restore from tape.

    If there's a way to differential without zeroing the logs I think that would do the trick.

    As for replication, we've looked at that already and is more than we can afford to spend at the moment.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't use differential backups so have no idea re the logs (I do suspect however that the answer to your question is "no") however another option is using a third party backup compression tool. You can back a free command line one from Idera (?? I think). A 30 GB backup will very likely come in around 6-8 GB. As a bonis your backup and restores will be quicker too.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    A Differential backup (In SQL Server terms) will not truncate the log. it is likely that Veritas is calling a Transaction Log backup a "differential" backup. Poor choice of terms on their part, really. Replication, Database Mirroring, and of course DoubleTake do not truncate transaction logs, so they would be usable in your setup. Transactional replication in fact holds transaction logs, until the transactions are successfully collected up. Replication, however, will not replicate structural changes (views, stored procedures, triggers, etc.).

Posting Permissions

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