by Ashish Upadhyay
To view Recovery Model setting, open the SSMS (SQL Server Management Server), right click on a database, and then select Properties. On the properties dialog box go to “Options” and you can see the Recovery Model in the right. Please refer below image for the same.
Recovery
Model in SQL Server is one of the important aspects of the database
configuration, as it determines the way transaction logs are going to maintain.
Microsoft has designed the recovery models to control transaction log
maintenance. Till the recent version of SQL Server 2012 (RCO) SQL Server has three
types of recovery models, simple, full, and bulk-logged.
Generally
user database runs on either full recovery model or simple recovery model
depending upon your server configuration while by default system database run
as:
System
Databases |
Default
Recovery Model |
Optional
Recovery Model |
Master |
Simple |
Full
and Bulk logged recovery model supported |
msdb |
Simple |
Full
and Bulk logged recovery model supported |
Model |
Full |
Full
and Bulk logged recovery model supported |
tempdb |
Simple |
Full
and Bulk logged recovery model is NOT supported |
Because
user database inherit all the properties or schema from the “model” database
obviously recovery model of your database will be same as recovery model of the
model database, which you can change later.
To view Recovery Model setting, open the SSMS (SQL Server Management Server), right click on a database, and then select Properties. On the properties dialog box go to “Options” and you can see the Recovery Model in the right. Please refer below image for the same.
Simple
|
Full
|
Bulk logged
|
In this simple recovery model,
transaction log backups are not allowed, that means you cannot setup
transaction log backup.
This model also automatically reclaims log space and keeps the log space requirement minimal. So there is almost no need to manage the transaction log space means less administrative overheads. Database can be restored only to the point of its last backup (Full and/or differential). All the transactions done after last backup need to be re-done. As a good practice keep the system database on simple (except tempdb as only full recovery model is allowed for tempdb), keep the read only databases, and/or the databases in DEV, TEST and STAGGING environment on simple recovery model seeing the importance of the data. |
The backup of the log file is
highly recommended in full recovery model.
No work is lost due to a lost or damaged data file and data can be recovered to an arbitrary point in time. Generally no data loss is expected until if the tail of the log is damaged. In this case changes since the most recent transaction log backup must be redone
In this model all operations
are fully logged, including bulk import operations.
As a good practice all the databases in the PRODUCTION Environment should be on the full recovery model with proper transaction log backup. Frequency of the transaction log backup may vary from one environment to other. |
Bulk logged recovery model is
an adjunct of the full recovery model that permits high-performance bulk copy
operations.
Because it minimal logging for most of the bulk logged operations for example: INSERT INTO, BCP commands, bulk insert, CREATE INDEX, ALTER INDEX etc., it requires less space for log. Loss of the data can recover to the end of any backup not to a specific point as point-in-time recovery is not supported in this model. As a good practice bulk logged recovery model can be used for heavy data load operation where performance of the operation is more important than recovery of the data. You can set your database to the “bulk Logged” at the start of the operation and re-set back to “simple” or “Full” model once bulk load operation is finish. Make sure to keep a full back up once you re-set the database to back to simple or full recovery model. |
No comments:
Post a Comment