Page 134 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes 9.5 Changing the Recovery Model

To change the recovery model (Transact-SQL)
Use ALTER DATABASE, as follows:

To set the database to the full recovery model:
USE master;
ALTER DATABASE database_name SET RECOVERY FULL;
To set the database to the bulk-logged recovery model:
USE master;

ALTER DATABASE database_name SET RECOVERY BULK_LOGGED;

Notes To change the default recovery model for new databases, use ALTER DATABASE to
change the recovery model of the model database.

9.6 Summary

Simple recovery model facilitates the maintenance of a database by making the transaction
log virtually maintenance free. There are limitations placed on the recoverability of a
database if this recovery model is used.
A database in this recovery model will have minimum logging for bulk import operations.
Space allocation and deallocation is only logged for bulk import operations. Point-in-
time and point-of-failure recovery may be possible when a database is in Bulk-Logged
Recovery model.

The full recovery and bulk-logged recovery models provide greater protection for data
than the simple recovery model. These recovery models rely on backing up the transaction
log to provide full recoverability and to prevent work loss in the broadest range of failure
scenario.

A database can be switched to another recovery model at any time. However, switching
from the simple recovery model is unusual. Be aware that if you switch to the full recovery
model during a bulk operation, the logging of the bulk operation changes from minimal
logging to full logging, and vice versa.

9.7 Keywords

Data Page: An SQL Server database’s basic data storage structure is 8 KB and is known as a data
page
Disaster Recovery Planning: The process of formulating, documenting, and testing the procedures
that would be performed if production data in one or more SQL Server databases were to be lost
or modified in an unforeseen disaster or malicious attack.
Extent: A collection of 8 data pages. Since a data page is 8 KB, an extent is 64 KB.
File Group: A logical grouping of SQL Server database files. By default, a new SQL Server
database contains the Primary file group.

128 LOVELY PROFESSIONAL UNIVERSITY