on 2014 Aug 15 2:46 AM
Hi,
I'm using SQLA 16 and I got strange behavior of dbbackup. If run dbbackup with "-xo" option to database which transaction log is mirroring, then dbbackup generate dateXX.mlg (e.g. 140815AA.mlg). I checked manuals, but I could not find the behavior. Is it design of dbbackup ? What is dateXX.mlg ? And is it possible to delete dateXX.mlg ?
Thanks. Tsuyoshi Yagi
Request clarification before answering.
MLG files are mirror log files.
Have you looked at: http://dcx.sybase.com/index.html#sa160/en/dbadmin/dbbackup.html*d5e54555 ?
The description of -xo says: Do not use this option if you are using database mirroring. See Transaction log file management in a database mirroring system.
Are you using database mirroring?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You are correct that database mirroring and transaction log mirroring are different cases. But if you are going to the trouble of mirroring the transaction log, it sounds like you want to ensure that you have a backed-up transaction log in case you lose the primary transaction log. So it doesn't quite make sense that you want to use -xo to trash the primary transaction log and/or the backed-up copy. You would not be able to recover your database from a transaction log if something were to happen to it.
it doesn't quite make sense that you want to use -xo to trash the primary transaction log
I agree. The use of dbbackup -xo implies "I DON'T CARE about rolling forward transaction log entries after restoring an old backup of the database file" which is OK in certain circumstances; e.g., an embedded application where the database can be recreated via other means whenever that is necessary.
However, the use of dbinit/dblog -m implies "I CARE SO MUCH about preserving transaction log entries that I'm willing to have two copies of the transaction log file maintained by the server" which is OK in other situations; e.g., your data is so precious to you that you're willing to do almost anything to protect it.
If dbinit/dblog -m is really desired, perhaps dbbackup -r or -x would be more appropriate.
As Breck suggests, -r is a good option. The steps outlined in the documentation for -r also apply to the mirrored transaction log.
The <dbname>.log file that is written to the backup directory that you specify with dbbackup is the fragment of transaction log going forward from the previous time you used -r.
To completely restore your database from the transaction logs only, you will use either the YYMMDDxx.log and <dname>.log files or the YYMNDDxx.mlg and <dbname>.mlg files.
Your alternative recovery method will be the backed up <dbname>.db file and its <dbname>.log fragment. However, this backup won't be up-to-date. You also won't be able to stage your recovery (for example, when you discover that something awful happened to one of your tables and you have already run your backup a few times).
This is why you don't want to erase your YYMMDDxx.log or YYMMDDxx.mlg files. Together with the current log/mlg file they comprise your backup.
Hi Jack and Breck,
Thank you for your reply.
If transaction log does not be mirrored, then dbbackup "-xo" does not create file.
Manual said -xo is "truncate log without backup" and manual does not said about "dateXX.log".
And if transaction log is mirrored and dbbackup "-r", then dateXX.log and dateXX.mlg (e.g. 140819AA.log 140819AA.mlg) is created.
Manual said "-r" create "dateXX.log".
So I think it is incorrect behavior (dateXX.mlg file created) when dbbackup -xo with transaction log mirroring.
By the way, I don't use SQL Remote Message Agent, so I think no problem about delete dateXX.mlg.
Thanks.
The manual does not say ANYTHING about what dbbackup does with the mirror log created by transaction log mirroring. You can complain about that fact (lack of documentation) but you can't say there's a bug if there's no specifications 🙂
If you want dbbackup -xo to stop creating .mlg files, then post an enhancement request on this forum, and give a business reason why you want that (we really want to know!)
One could interpret the current behavior as -xo will truncate the primary transaction log, but if you are mirroring the transaction log, then you must really want a backup so we will do the "-r" thing to the mirroring transaction log, rather than just blow it away. The documentation does not speak about this behavior but it does have the following statement.
"Using this option can result in a database that cannot be recovered from media failure."
It uses the word "can" rather than "will". The "can" case applies when you aren't mirroring the transaction log.
You haven't really said why you are using transaction log mirroring in the first place. If you want -xo to apply to both logs, why use transaction log mirroring at all?
The manual does say ANYTHING about what dbbackup does with the mirror transaction log.
Am I right that you mean "does not say ANYTHING"? - That's just in order to avoid further confusion between "transaction log mirroring" (which is not referenced on that doc topic) and "database mirroring" (which is). - Besides that, I certainly agree fully on your and Jack's point of view.
You, me, Volker :)...
If you think the "strange behaviour" you have observed w.r.t. the mirror log file should be documented, then I'd suggest that you add a comment on the according topic in the DCX page, it's easy to do:) - If it has puzzled you, it may puzzle others, so a comment would seem worthwhile.
At least that's my personal view on DCX comments when features have been a surprise to me, even when someone has explained their reason, as Jack and Breck have done here...
User | Count |
---|---|
52 | |
6 | |
5 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.