555
Chapter 21: Backup and Recovery Planning
21
You can determine the current recovery model for every database from the following query using the sys.databases cata-
log view:
SELECT name, recovery_model_desc
FROM sys.databases;Modifying Recovery Models
Although a production user database is typically set to a full recovery model,
there’s nothing to prevent you from switching between recovery models during an
operation to optimize performance and suit the specifi c needs of the moment.It’s perfectly valid to run during the day with the full recovery model for transaction
durability and then to switch to bulk-logged during data imports in the evening.During recovery it’s the full, differential, and transaction-log backups that count. The
recovery operation doesn’t care how they were made.Because the simple recovery model does not permanently log the transactions, care must be
taken in switching to or from the simple recovery model:■ If you switch to simple, the transaction log should be backed up prior to the
switch.
■ If you switch from simple, a full database backup should be performed immediately
following the switch.
■ Schedule regular transaction log backups and update your recovery plans.Backing Up the Database
The actual process of performing a backup presents as many options as the underlying
concepts present.Backup Destination
A backup may copy the data to one of two possible destinations:■ Disk subsystem: You can perform a backup either to a local disk (preferably
not the same disk subsystem as the database fi les) or to another server’s disk
drive by using the Universal Naming Convention (UNC). The SQL Server ser-
vice account must have write privileges to the remote drive/share to save the
backup fi le.c21.indd 555c21.indd 555 7/31/2012 9:22:47 AM7/31/2012 9:22:47 AM
http://www.it-ebooks.info