The ‘Database Properties’ window will open. Once the database folder is expanded, right click on the database and select the ‘Properties’ option. In SQL Server Enterprise Manager, open the ‘Databases’ folder. SQL Server 2000 Enterprise Manager directions to configure the database Recovery Model: In addition, the Bulk-Logged Recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes. Under the Bulk-Logged Recovery model, a damaged data file can result in having to redo work manually based on the operations above that are not fully logged. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, Bulk load operations, CREATE INDEX as well as text and image operations. The Bulk-Logged Recovery model provides protection against failure combined with the best performance. All operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged and recoverable. If one or more data files are damaged, restoring the backups permits recovery of all committed transactions using a combination of the database and transaction log backups.įull Recovery provides the ability to recover the database to the point of failure or to a specific point in time. ![]() The Full Recovery model uses database backups and transaction log backups to provide complete protection against failure. The Simple Recovery model is easier to manage than the Full or Bulk-Logged models, but at the expense of higher data loss because the contents of the database transaction log are truncated each time a checkpoint is issued for the database. Transaction log (or incremental changes) backups are not available. With the Simple Recovery model, data is recoverable only to the most recent full database or differential backup. The three database recovery model options are: These settings can be configured either through Enterprise Manager or through T-SQL using the ALTER DATABASE command. ![]() The appropriate choice depends on your applications and the criticality of your data. SQL Server 2000 offers three recovery models that can be implemented for your databases. ![]() Which recovery model is best for the databases in your environment? This setting depends on the critically of the data and the acceptable data loss in the event of a system failure. This document analyzes the various options available for your backup and recovery process with SQL Server 2000 as well as an enhancement to your SQL Server backup and recovery process using a highly efficient backup and restore utility that provides significant time and disk space savings called SQL LiteSpeed. Several options are available for defining your backup and recovery model and your choices will determine the reliability and the amount of data loss your company can acceptably incur. One of the most important aspects for a database environment is ensuring reliable backups are being executed and a dependable recovery plan is established in the event of a system failure or data corruption.
0 Comments
Leave a Reply. |