Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stairway to Transaction Log Management in SQL Server, Level 5: Managing the Log in Full Recovery Mode Expand / Collapse
Author
Message
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: Wednesday, April 16, 2014 9:53 AM
Points: 569, Visits: 985
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
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: Thursday, September 05, 2013 6:24 AM
Points: 8, Visits: 63
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
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: 2 days ago @ 1:35 PM
Points: 1,381, Visits: 2,004
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
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: 2 days ago @ 1:35 PM
Points: 1,381, Visits: 2,004
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
Posted Friday, January 27, 2012 6:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 3:03 AM
Points: 61, Visits: 384
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
Posted Friday, January 27, 2012 12:17 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: 2 days ago @ 5:51 PM
Points: 904, Visits: 4,450
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
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: 2 days ago @ 1:35 PM
Points: 1,381, Visits: 2,004
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
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: Wednesday, April 09, 2014 10:36 AM
Points: 896, Visits: 1,479
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
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: Wednesday, April 16, 2014 9:53 AM
Points: 569, Visits: 985
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
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: Wednesday, April 16, 2014 9:53 AM
Points: 569, Visits: 985
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 12»»

Permissions Expand / Collapse