Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565

    Unanswered: Backup Takes MSSQL 2000 DB offline

    ok, here is an interesting one.
    We have multiple jobs that run the following SQL Backups to Permanent Backup devices.

    Backup Database DBX to DBXFull with NoInit - 1 am
    Backup Database DBX to DBXDiff with Differential, NoInit - 12 pm
    Backup Log DBX to DBXLog with NoInit - every hour on the hour
    the devices are in path E:\SQLBackups\<databasename>
    E: is on a SAN

    Every Night E: is backed up by legato, When Legato Starts to backup the directory, at the hour a transaction log backup occurs. The Backup Fails and The Database Is Taken OFFLINE.

    Questions are as follows.
    • What is causing the db to be taken offlline?
    • How can i stop the db from being taken offline while still mainting my current backup strategy.


    Temporarily we have stopped tlog backups during the directory backup phase (legato) and then resume them when it is complete.

    Difficulty Level: we will not be using Legato's SQL Plugin for the backups, they have to be performed with jobs and SQL Backup.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Never heard of legato, but my guess would be that is sees and handles the error, and takes the db offline to stop any processing so you can fix it, because it believes it critical...which it well might be..

    just a thought

    Have you ever thought of changing your name to Kurt?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And I thought this was a good read

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=43147
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Quote Originally Posted by Brett Kaiser
    Never heard of legato, but my guess would be that is sees and handles the error, and takes the db offline to stop any processing so you can fix it, because it believes it critical...which it well might be..

    just a thought

    Have you ever thought of changing your name to Kurt?
    nope but you can call me Mr. Wolf.

    Legato backup software is just copying the contents of the e: drive and is not doing any SQL Backups. so it has not access to SQL 2k
    the issue appears to be SQL taking the db offline because the T-log job cannot complete. as you know, this should not happen, the job should just indicate a failure in it's job history.

    and in answer to your subsequent post
    i am not using sqlmaint.exe for my jobs. that was causing other issues to begin with.

    anyone? buehler buehler?

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Are you getting this error?:

    Msg 3242, Level 16, State 1, Line 1

    The file on device '%s' is not a valid Microsoft Tape Format backup set.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP LOG is terminating abnormally.

    Or something like this?:

    BACKUP failed to complete the command ...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Quote Originally Posted by rdjabarov
    Are you getting this error?:

    Msg 3242, Level 16, State 1, Line 1

    The file on device '%s' is not a valid Microsoft Tape Format backup set.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP LOG is terminating abnormally.

    Or something like this?:

    BACKUP failed to complete the command ...

    not at this time
    I will look through prev logs and see

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I did try to replicate the situation by locking the file that acquires the appended tlog backup, but just got the error. The database never went offline...I think you're making it all up
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Doubt me all you want. all the great ones had detractors.

    Napoleon, Dan Quayle, Deion Sanders what if they had given up every time someone slammed them? I think we would live in world not as good as the one we have today, so i will persevere. i will continue to fight the good fight and ......

    forget it , i ran out of steam.

    does anyone have any hypothosisessesees (i can never end that word)?
    Last edited by Ruprect; 12-02-04 at 16:27.

  9. #9
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    here is an update

    just to see we stopped all jobs from backing up between 10 and 1 last night and the database still was placed in an offline state.
    hmmmmm.

    let me pose this question.
    if the directory structure of the sql server was all located in the same root directory on the san

    Example
    SQL server 2000 are in E:\Mssql
    Backup Devices are in E:\SQLBackups

    The nightly backup that is run on the san backs up the entire E:\ directory,

    So my Question is, what would that do to the sql 2000 databases and the system files? In other words, would the legato backup of the sql server 2000 installation directory cause a LIVE sql server 2000 to take any dbs offline?

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, just a thought, - is AutoClose set to ON on the database by any (silly and unbelievable) chance?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    guess what? good guess. the server was originaly loaded as sql personal.
    then an enterprise edition upgrade was performed.
    the db was set to autoclose.
    which leads me to suspect one thing......... that you RDJabarov set my db to autoclose in the library with a candlestick...

    creepy

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Well, I haven't got a Clue on this one, but I thought he did it in the kitchen with the revolver.

    -PatP

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Wrong, I don't use my revolver for that! Only to play Russian Roulette
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    someone get the gasoline! there is going to be a flame war....

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, it started long time ago, we're just measuring the distance now to make sure there is no wounded left behind alive
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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