Transcript PASS Pre-Conf Workshop

Understanding
Logging & Recovery
What Programmers Always Avoid Until it’s too Late!
Kimberly L. Tripp
Principal Mentor, Solid Quality Learning
at www.SolidQualityLearning.com
President, SYSolutions, Inc. at www.SQLSkills.com
Email me: [email protected] and [email protected]
Speaker – Kimberly L. Tripp



Independent Consultant/Trainer/Speaker/Writer
President, SYSolutions, Inc. www.SQLSkills.com
Principal Mentor, Solid Quality Learning
* In-depth, high quality training around the world!
www.SolidQualityLearning.com



SQL Server MVP (http://mvp.support.microsoft.com/)
Microsoft Regional Director
(http://www.microsoftregionaldirectors.com/Public/)
Writer/Editor for TSQL Solutions/SQL Magazine
www.tsqlsolutions.com and www.sqlmag.com



Coauthor for MSPress title: SQL Server 2000 High
Availability
Presenter/Technical Manager for SQL Server 2000 High
Availability Overview DVD (MS Part# 098-96661)
Very approachable. Please ask me questions!
Possible Combinations
From
To 
Full
Bulk_Logged
Simple


Full
n/a
Bulk_Logged
Simple
No Change Required,
Full Database or
Differential backup after
change
Transaction Log Backup
Recommended AFTER
Transaction Log Backup
Recommended BEFORE
n/a
Full Database or
Differential backup after
change
Backup Log just prior
to change
Backup Log just
prior to change
n/a
No Change Required,
Full  BULK_LOGGED doesn’t break automated backup procedures or log
shipping. However, a log backup immediately before (when changing to Bulk
Logged) and a log backup immediately after (when changing back to full) are
recommended.
Transitions to/from SIMPLE are NEVER recommended as they break the continuity
of the log chain. This eliminates your ability to go back to a prior full…meaning
you’re only as good as your last full backup!
Resources



Check out www.sqlskills.com for information about
upcoming events, useful downloads and excellent
scripts! There are quite a few resources and/or links
to use.
MSPress title: SQL Server 2000 High Availability
Authors: Allan Hirt with Cathan Cook, Kimberly L.
Tripp, Frank McBath
ISBN: 0-7356-1920-4
Check out the main page of
www.sqlskills.com for a
sample chapter to download!
Resources


See www.SQLSkills.com for additional sample code
Articles in SQL Server Magazine, Aug/Sept 2003:
Recovering from Isolated Corruption
The Best Place for Bulk_Logged, InstantDoc #39782

Articles in SQL Server Magazine, Sept 2002:
Before Disaster Strikes, InstantDoc ID#25915
Log Backups Paused for Good Reason, InstantDoc #26032
Restoring After Isolated Disk Failure, InstantDoc #26067
Filegroup Usage for VLDBs, InstantDoc #26031

Articles in TSQLSolutions Journal, Oct 2001:
TSQLTutor Quick Tip:
Saving Production Data from Production dBAs,
InstantDoc ID#22073
Resources






See www.sqlmag.com and www.tsqlsolutions.com
for articles on Backup/Restore
From Books Online “Home Page” select White Papers
to get to msdn
For Tech Net articles use:
http://www.microsoft.com/technet/prodtechnol/sql/d
efault.asp?frame=true
See www.microsoft.com/sql for all sorts of stuff!
Support Resources listed:
http://www.microsoft.com/sql/support/default.asp
Webcasts:
http://support.microsoft.com/default.aspx?PR=pweb
cst&FR=0&SD=MSDN&LN=ENUS&CT=SD&SE=NONA
BOL Favorites









Make sure to get the latest version of the BOL. See
www.microsoft.com/sql, Technical Resources, Product
Documentation
“Switching Recovery Models”
“ALTER DATABASE”
Functions: OBJECTPROPERTY, SERVERPROPERTY,
DATABASEPROPERTYEX
“Using Recovery Models”
“Checkpoints and the Active Portion of the Log”
“Virtual Log Files”
“Shrinking the Transaction Log”
“Optimizing Transaction Log Performance”