565
Chapter 21: Backup and Recovery Planning
21
■ (^) A list of all active transactions
■ Information about the minimum work required to roll back the database
■ (^) Marks the space before the oldest uncommitted transaction in a database with
simple recovery for reuse
■ (^) Writes all dirty data and log pages to disk
Basically, a checkpoint gets everything up to date as best as it can and then records the
current state of the dividing line between active and inactive in the log.
In SQL Server 2012 there is a new type of checkpoint called an indirect checkpoint. This is actually a database-level
setting in which you can manually specify a custom recovery point for a given database. By default the recovery
interval is 0, which means SQL Server performs checkpoints on that database using the interval set by the recovery
interval server option. For more information on Checkpoints see http://msdn.microsoft.com/en-us/
library/ms189573.aspx.
Backing Up the Transaction Log
Performing a transaction log backup is similar to performing a full or differential backup,
with a few notable differences.
The T-SQL command is as follows:
BACKUP LOG AdventureWorks2012
TO DISK = 'e:\AdventureWorks2012Backup.bak'
WITH
NAME = 'AdventureWorks2012Backup';
Result:
Processed 2 pages for database 'AdventureWorks2012', file
'AdventureWorks2012_Log' on file 2.
BACKUP LOG successfully processed 2 pages in 0.118 seconds (0.095
MB/sec).
The same media options apply to the transaction log backup that apply to the database
backup; in addition, two options are transaction-log specifi c.
■ (^) NO_TRUNCATE\CONTINUE_AFTER_ERROR: Used for backing up the tail of the log
of a damaged database that is offl ine and does not start. If the data fi les of a user
database are damaged, a tail log backup succeeds only if the transaction log fi les
are not damaged, the state of database supports tail log backup, and the database
does not contain any bulk logged operations.
■ (^) NORECOVERY: Used to back up the tail of the log of a database that is online, and
you intend to perform RESTORE next.
c21.indd 565c21.indd 565 7/31/2012 9:22:50 AM7/31/2012 9:22:50 AM
http://www.it-ebooks.info