Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Tony Davis » Stairway to Transaction Log Management in SQL...
Add to briefcase
16 posts, Page 1 of 2
12»»

Stairway to Transaction Log Management in SQL Server, Level 5: Managing the Log in Full Recovery Mode Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Tony Davis
Posted Wednesday, June 01, 2011 8:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Yesterday @ 9:24 AM
Points: 511, Visits: 948
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
Post #1118065
pjs52
Posted Friday, January 27, 2012 6:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 5:48 AM
Points: 8, Visits: 61
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?
Post #1242773
Megistal
Posted Friday, January 27, 2012 6:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:23 AM
Points: 1,251, Visits: 1,796
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
Post #1242775
Megistal
Posted Friday, January 27, 2012 6:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:23 AM
Points: 1,251, Visits: 1,796
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.
Post #1242777
vliet
Posted Friday, January 27, 2012 6:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 1:08 AM
Points: 47, Visits: 275
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!
Post #1242788
jasona.work
Posted Friday, January 27, 2012 12:17 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 1:05 PM
Points: 490, Visits: 2,452
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
Post #1243089
Megistal
Posted Friday, January 27, 2012 12:38 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:23 AM
Points: 1,251, Visits: 1,796
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.
Post #1243099
codebyo
Posted Sunday, January 29, 2012 7:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 8:50 AM
Points: 862, Visits: 1,440
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
Post #1243377
Tony Davis
Posted Monday, January 30, 2012 3:56 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Yesterday @ 9:24 AM
Points: 511, Visits: 948
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.
Post #1243491
Tony Davis
Posted Monday, January 30, 2012 4:13 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Yesterday @ 9:24 AM
Points: 511, Visits: 948
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.
Post #1243494
« Prev Topic | Next Topic »

Add to briefcase
16 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.