Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Exclamation Unanswered: SQL Server 2000 Backup / Restore Bug

    Hello!

    I have a bug in SQL Server 2000 that I would like to discuss.

    The bug is described briefly on http://support.microsoft.com/kb/821334.

    "The potential for inconsistency in the backup history tables backupset and backupmediafamily is resolved. This issue may cause the restore process to point to the wrong backup files."

    According to my experiences the bug is extremely serious. SQL Server backup and restore operations are not fully reliable. If you have a disaster situation there is a possibility that you can not perform a restore.

    Technically a media_family_id is generated in a backup operation and inserted into msdb.dbo.backupmediafamily. What could happen is that a media_family_id, that already exists in backupmediafamily, is generated.

    The bug is fixed from version 8.00.0859, but only if you have an special undocumented trace flag (3003) enabled.

    Here is the information that I have got from Microsoft about this trace flag.
    "Trace flag 3003 changes how SQL generates media_set_id for new backup files. It will guarantee that new media_set_id values in backupset table are unique when you create a new file with your backup. This is the only place in code where TF 3003 is used so it will not impact your SQL Server operations."

    I have a SQL Server with 80 databases. I have a maintenance plan with full backup every 24 hours and log backup every 5 minutes.

    The error happened on my server a few times every 24 hours. I believe it's a general problem.

    Best regards

    Ola Hallengren

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This does not appear to me to be a serious issue. Data stored in msdb is not required for restores. It is just facilitates using Enterprise Manager's GUI-based database management utility. You can still restore individual backups and logs, even to point-in-time, without entries in msdb.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    8
    I am aware of that I can perform a restore not using the backup - tables in the MSDB database.

    However when you take frequent log backups, it's not the easiest thing to write a restore script with a few hundred files that should be applied in the right order. I would like to avoid that in a disaster situation.

    Therefore I think it's good that SQL Server keeps track of this information in the MSDB database. Enterprise Manager is then using these tables for convinient restore. It's also possible to write custom restore scripts that are using these tables. (I have made a stored procedure that takes a database name and a restore time as parameters and then generates a restore script based on the information in the MSDB database.)

    SQL Server provides this feature and I think that many people rely on it in their restore plans. Therefore I think that it should be fully reliable.

    I think that implementing a bugfix as a trace flag is a good idea in many situations, but not in this one. If you run into this bug the first time in a disaster situations, it's not much help to hear that it could have been avoided with an unknown trace flag.

    I understand that their could be workarounds but I think that if Microsoft is serious about SQL Server as a database for Enterprise solutions, then they can not have this kind of bugs.

    Thanks for your input. My intention with this thread was to see if people were aware of this.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Maybe it is a "bug", but I take issue with calling it a "serious bug", when it does not prevent database recovery. Enterprise Manager is the GUI interface provided by SQL Server for convenient adminstration, and I can tell you from experience that it is far and beyond the interface provided by Oracle, both in terms of ease of use and functionality. But you can still run your database server completely from the command line if you wish.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can't remember the last time I used the GUI tools in a disaster-like situation.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Posts
    8
    I think that you don't get me right.

    When sql server is executing the backup command it's also keeping track of
    the backup operation in the tables backupmediaset, backupmediafamily and backupset in the MSDB database.

    These tables can then be used to write powerful restore scripts that can be used in a disaster situation.

    If sql server is not keeping consistent data in these tables,
    then it's not a good thing if your restore scripts relies on them.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think I understand perfectly, but feel free to clarify if you think I'm missing something.

    If you do periodic full backups with a name like fulDBNAMEyyyymmddhhmm. followed by periodic log backups with names like logDBNAMEyyyymmddhhmm then you can easily write a short script (Perl, VBS, whatever tool trips your trigger) that will take any collection of database backup files and rebuild a working database from them for you. You can obviously play with the names to meet your local conventions. If you get creative, you can even set things up so that the files are zipped so that each full database starts a new zip file and the log backups that follow it get zipped in with it.

    At least in my opinion the task borders on the trivial. Once you've got it working, you can easily expand it to cover as many databases as you need. This gives you a a simple, efficient, and pretty much fool-proof way to deal with the backups.

    -PatP

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm suspecting the "O" in Ola's login stands for "O"racle...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Posts
    8
    PatP, I think that your solution is very fine.
    My solution with tsql code using the backup - tables in the MSDB database is also a good solution.

    I'm not saying that mine is better or that I can not make another solution.

    My restore script failed the other day, I got irritated and I started to investigate it.

    My point is just that if data in these tables can not be relied on
    then Microsoft should be clear about that.

    blindman, I'm generally very happy about SQL Server and I'm not in favor of Oracle.

    Thanks for your respons.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I appreciate the things that the GUI tool team do, but I rarely rely on those tools. The whole idea behind a GUI to to make the 80% of functionality that is used by most people as easy to use as possible. Reliability is important, but it isn't (and probably can be) as dependable as the more basic system tables.

    I understand that you're unhappy about the problems with the backups, and you've got good reason to be unhappy. I agree that it ought to be fixed, and that a trace flag doesn't seem like an adequate answer to me either. I didn't know about the problem even though I use a lot of Microsoft tools... I just rarely use the GUI to manage SQL Server.

    -PatP

Posting Permissions

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