Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
beatlejuice95
Starting Member
2 Posts |
 Posted - 2011-11-30 : 12:41:25
|
| I need some help understanding the TRN file format, and the Backup Log command.Here for background is my problem. We currently run nightly backups to a BAK, and then once an hour we write a new TRN file with the hourly transactions. This I understand, and makes sense.Today I started looking at our code to re-index our own structures, and I'm noticing we are looping through all known indexes of our database, calling indexdefrag against the object, then we are calling:backup log <dbname> to disk = '<somefilename>'Potential problem I'm seeing here is that <somefilename> is never changed, and we keep calling backup log after each index and writing to the same file.Question I have is if this is allowed, and is the output TRN valid in that it appears to be appending each set of transaction logs to this file. I don't know if this file is in a usable state, ie. could we recover from this file? The TRN file itself is huge, and I'm not sure if this is the most efficient way to do this.I'm hoping an experienced DBA reads this and can tell me if this is fine, or should it be done differently |
|
|
sandy123
Starting Member
14 Posts |
Posted - 2011-11-30 : 12:59:00
|
| if your backup query has option like INIT or format then it will override the backups on existing media. if not you can run "RESTORE HEADERONLY" to get the list of backups.sandy |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-30 : 19:20:44
|
You can "append" multiple log backups to a single file. However we never do. It adds confusion during a restore, and a single corruption can render the whole file useless.We use a Stored Procedure to make backups, and then just execute that stored procedure when we want to make a backup, This ensures that:1) File naming convention is consistent2) Files are placed in a suitable folder (and if that changes we only have one place in the code to change)3) Anyone who wants to make a "just in case" !! backup uses the SProc, and thus we don't have adhoc backups scattered around the diskHaving all the Trans backups in the correct folder is important so that:1) They get backed up to tape  2) You can find them all if you want to restore.having said all that I don't think what you have is a good idea. Its forcing a Trans backup BETWEEN index rebuilds/reorganisations. This IS important because Index Rebuild is often the most transaction log intensive task. However, on a big index the rebuild will still generate a huge amount of Trans Log data (before the next backup) and for small tables you are backing up the transaction log with maybe very little data in it.We tackle this issue differently; we scheduled a Transaction log backup every 2 minutes during the Index Rebuild scheduled task period. We allow an hour for this (but in fact it currently takes about 45 minutes - so we get several very small Trans Log backups at the end of that period) |
|
|
|
|
|
|
|
|
Backup Log question