June 1, 2011 at 8:10 am
Comments posted to this topic are about the item Stairway to Transaction Log Management in SQL Server, Level 5: Managing the Log in Full Recovery Mode
January 27, 2012 at 6:02 am
Nice writeup!
I'm moving past the novice DBA stage and have been use the .trn extension on all transaction log backups for past few years. Is there a good case for using either .bak or .trn over the other? Or is it merely preference?
January 27, 2012 at 6:11 am
First nice article!
Second, must I understand that the only way in SQL 2008 and Up to break the log chain is to switch to simple recovery model?
Pre-SQL Server 2008, there were a couple of command, namely BACKUP LOG WITH NO_LOG or BACKUP LOG WITH TRUNCATE_ONLY (they are functionally equivalent) that, when issued, would force a log file truncation and so break the log chain
Ty
January 27, 2012 at 6:13 am
Peggy, the file extension is only for your convenience. Default full backup are .bak, transaction log are .trn but you can defined them as you wish.
January 27, 2012 at 6:30 am
Nice article.
I have been called once by power users after they had started a batch with incorrect parameters, and another time when they started the batch accidentaly while they only wanted to inspect some results of the previous batch (due to a flaw in the user interface design). On both instances I was able to restore the database using a point in time recovery to just before the start of the batch. These batches are run after office hours so no user modifications were lost.
I can tell you, most DBA's don't know about this possibility, but it can really save the day when a user initiated disaster strikes. At another client, we could not risk throwing work of other users away, but we could make a restore to another database and copy only the relevant data without affecting the integrity. It required soma support from the developers of the application to restore just enough, but I've seldom seen users so happy!
Yes, point in time recovery is very very useful, especialy when you need to recover from errors caused by user initiated actions. Thank you, Microsoft, for putting that feature into SQL Server!
January 27, 2012 at 12:17 pm
Megistal (1/27/2012)
First nice article!Second, must I understand that the only way in SQL 2008 and Up to break the log chain is to switch to simple recovery model?
Pre-SQL Server 2008, there were a couple of command, namely BACKUP LOG WITH NO_LOG or BACKUP LOG WITH TRUNCATE_ONLY (they are functionally equivalent) that, when issued, would force a log file truncation and so break the log chain
Ty
My understanding is that the log chain starts at the last full DB backup. So if someone runs a full backup of the DB in the middle of your backup cycle, then deletes that full backup, your log chain is broken.
If there is a need to run a DB backup, but you don't want your chain to be broken, running a COPY ONLY DB backup will work.
BACKUP DATABASE {DBName} TO DISK = '{Backup location}' WITH COPY_ONLY
January 27, 2012 at 12:38 pm
jasona.work (1/27/2012)
Megistal (1/27/2012)
First nice article!Second, must I understand that the only way in SQL 2008 and Up to break the log chain is to switch to simple recovery model?
Pre-SQL Server 2008, there were a couple of command, namely BACKUP LOG WITH NO_LOG or BACKUP LOG WITH TRUNCATE_ONLY (they are functionally equivalent) that, when issued, would force a log file truncation and so break the log chain
Ty
My understanding is that the log chain starts at the last full DB backup. So if someone runs a full backup of the DB in the middle of your backup cycle, then deletes that full backup, your log chain is broken.
If there is a need to run a DB backup, but you don't want your chain to be broken, running a COPY ONLY DB backup will work.
BACKUP DATABASE {DBName} TO DISK = '{Backup location}' WITH COPY_ONLY
Doing a full backup in the middle of a chain won't break it. You can still take the previous full and apply all logs up to the current point if no log is missing.
The log is filled out when a transaction log backup is being taken not by a full.
At least this is a behavior in 2008 and I would guess it's the same in 2005.
January 29, 2012 at 7:56 pm
Excellent article and highly practical examples! Thank you!
One question though: when having to restore to a specific point in time using STOPAT can I trust the "BackupStartDate" column returned by the following code?
RESTORE HEADERONLY FROM DISK = 'C:\BACKUP_TESTE\AdventureWorks2008R2_20110927_LOG.BAK';
That would be an easier way to identify which log backup I really need to stop at before performing the actions?
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
January 30, 2012 at 3:56 am
Hi Peggy,
Yes, I used .bak for all backup files in the article but as you say, the more common convention, which I should probably have followed, is to use .bak for database backups and .trn for transaction log backups.
Cheers,
Tony.
January 30, 2012 at 4:13 am
My understanding is that the log chain starts at the last full DB backup. So if someone runs a full backup of the DB in the middle of your backup cycle, then deletes that full backup, your log chain is broken.
Hi Jasona,
If someone deleted the full backup, you could still go back to the backup before that, and then restore your chain of log files, since each log backup contains all the records generated since the last log backup. However, if you take differential backups, then any that rely on the deleted base full backup will be unusable.
Copy only backups allow you to take a full backup outside of your scheduled backup routine, and without disrupting your diff backups. Gail Shaw has a nice article on this: http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/
Cheers,
Tony.
January 30, 2012 at 4:48 am
Hi Megistal,
I'd be interested to learn why you'd like another way to force log truncation, other than temporarily switch the database to SIMPLE? It's true that the techniques you suggest are now deprecated.
If you surf around you'll probably find reference to an alternative, which is BACKUP LOG TO DISK='NUL'. However, it's a very bad practice. It basically takes a log backup and just discards the contents, without SQL Server being aware of the fact. You can continue to take normal log backups after this but they will be useless as the log chain is broken. Jonathan Kehayais covers this well in Chapter 8 of his "Troublshooting SQL Server book[/url]" (the eBook is currently a free download).
Cheers,
Tony.
January 30, 2012 at 6:50 am
Tony Davis (1/30/2012)
If you surf around you'll probably find reference to an alternative, which is BACKUP LOG TO DISK='NUL'.
Wasn't aware of this one, could be handy one day or another, more knowledge is always appreciate!
Tony Davis (1/30/2012)
Jonathan Kehayais covers this well in Chapter 8 of his "Troublshooting SQL Server book[/url]" (the eBook is currently a free download).
Thank you for the book reference, I'll take a look.
Tony Davis (1/30/2012)
Hi Megistal,I'd be interested to learn why you'd like another way to force log truncation, other than temporarily switch the database to SIMPLE? It's true that the techniques you suggest are now deprecated.
Altering the database that way (FULL -> SIMPLE -> FULL) flush the plan cache and this is quite a problem. Therefore if I can find something similar to the switch recovery model (which has proved to be the best option so far, unfortunately) I'll be glad to avoid that plan cache flush.
January 30, 2012 at 7:59 am
Tony Davis (1/30/2012)
My understanding is that the log chain starts at the last full DB backup. So if someone runs a full backup of the DB in the middle of your backup cycle, then deletes that full backup, your log chain is broken.
Hi Jasona,
If someone deleted the full backup, you could still go back to the backup before that, and then restore your chain of log files, since each log backup contains all the records generated since the last log backup. However, if you take differential backups, then any that rely on the deleted base full backup will be unusable.
Copy only backups allow you to take a full backup outside of your scheduled backup routine, and without disrupting your diff backups. Gail Shaw has a nice article on this: http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/
Cheers,
Tony.
Tony, after the first reply to my comment, I remembered that at some point, this had been explained to me once before (I can't find the original though) And here I thought I was *SO* smart...
😉
Thanks,
Jason
January 31, 2012 at 5:10 am
One question though: when having to restore to a specific point in time using STOPAT can I trust the "BackupStartDate" column returned by the following code?
That would be an easier way to identify which log backup I really need to stop at before performing the actions?
Hi Andre,
Yes, this is quite a tricky area. That query might at least help you identify that this was the log backup taken right after whatever the "unfortunate event" happened to be. The degree of difficulty of a p-i-t restore rather depnds though on how accurately you know the exact time the problem occurred. The best discussion of an alternative that I've found, if you're really unsure of the time, is based on restoring to an LSN instead: http://janiceclee.com/2010/07/25/alternative-to-restoring-to-a-point-in-time/.
Cheers,
Tony.
January 31, 2012 at 5:23 am
Andre,
Just stumbled across this post: http://stanleyjohns.wordpress.com/2012/01/09/reading-the-transaction-log-using-fn_dblog-and-fn_dump_dblog/
Talks about how to peek inside a log backup using fn_dump_dblog, which can show you what times are contained within. Completely unsupported and undocumeted function, so use entirely at your own risk etc. etc. 😉
But it was new to me, and seemed useful, so thought I'd share.
Cheers,
Tony.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply