Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: backups interfering with log shipping?

    I have some questions on backups and log shipping.

    Before I get to them, though, the goal:

    Back up a SQL 2000 database (~5GB in size) completely every day.
    Back up its transaction log hourly.
    Maintain a warm backup DB server using log shipping.

    Here is what I have done so far, which has led to my questions:

    I have stopped using maintenance plans for backups, feeling that
    then the process would be less 'black boxy'

    I first created two jobs to perform the daily db and hourly tlog backups
    that saved the backups in files using the naming formats
    <name>_db_<yymmdd>.bak and <name>_tlog_<yymmddnnss>.bak.

    Issue 1: doing backups this way means that it's a little difficult for the
    log shipping jobs to figure out the filenames (esp. the tlog ones), if log
    shipping is going to use the files generated by the backup jobs.

    Issue 2: I thought maybe I'd have log shipping generate its OWN backup
    files, but would that cause problems with the transaction log? Say the
    normal tlog backup fires, then fifteen minutes later the shipping tlog
    backup fires. Would the shipping tlog backup file be missing the transactions
    that were backed up during the normal log backup?

    Issue 3: To try to help with the naming issue, I tried switching the backups from
    creating new files each time to file devices whose names would be constant.
    This worked, but since my database is about 5GB in size, that meant
    that, with expiring backups after 7 days, the database backup device
    would settle in at about 25GB (I skip weekends) and I'd have to copy
    or cab-copy-uncab that file over to my warm server every day. That
    seemed a little inefficient. Does anybody have alternative ideas?

    My last question is for general info:

    Where does SQL Server keep the information on the backups that exist?
    Whether I was using individual files or file devices, I was able to go to
    Database-All Tasks-Restore Database... in Enterprise Manager and it
    would show me the backups that existed. I imagine these must be
    stored in a system table somewhere, but I did not see any obvious place
    to look (no 'sysbackups' table, etc.).

    Many thanks in advance for your input!


  2. #2
    Join Date
    Jan 2004
    Boiling my above messgae down to bite-size questions:

    1. Where does SQL store information related to the backups that currently exist? Or does SQL scan for backup files and figure out what's in them on the fly (seems unlikely to me)?

    2. Say I want to run two transaction log backups per hour. One is included in the normal backup plan and one occurs, say, 15 minutes later for log shipping purposes. Is my log shipping going to fail because the transactions that were backed up during the normal bacup will not be in my log shipping backup?

  3. #3
    Join Date
    Sep 2004
    It's in the msdb in a table called backupset.

    I run log shipping where I backup the transaction log to a file name like DBNAME_TRAN.TRN. I then copy one to the SHIP TO server and apply it while copying the other to a directory where I add the data and time. I have scripts that determine which logs to apply in case I have to restore the database from the Full, Incrimental and then apply transaction log backups.

Posting Permissions

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