Recovery models for SQL Server
Recovery models for SQL Server
|
| Release 9.3 |
   |
Note:This topic was updated for 9.3.1.
NOTE: Applies to geodatabases created with an ArcGIS Server Enterprise license only
For SQL Server, the recovery model is a database setting, changed either through the Enterprise Manager, Management Studio, or the ALTER DATABASE statement. There are three recovery models for SQL Server: full, bulk-logged, and simple. Each is summarized below.
NOTE: For geodatabases stored in an ArcSDE database server, you do not change the recovery model.
- Full recovery model
Full recovery allows point-in-time (or point-of-failure) recovery, provided you have the transaction log backups at that point in time. Point-in-time recovery signifies the ability to recover a database to a specific time, restoring all committed transactions and rolling back all incomplete transactions.
Recovery to a point of failure or point in time involves
- Backing up the currently active transaction log
- Restoring the most recent full database backup without recovery
- Restoring the most recent differential database backup without recovery
- Restoring in sequence any transaction log backups without recovery
- Restoring the last transaction log backup with recovery
- Bulk-logged recovery model
Databases using the bulk-logged recovery model minimally log bulk operations to the Microsoft SQL Server transaction log. These operations include CREATE INDEX, SELECT … INTO, writetext, updatetext, and BULK INSERT. The transaction log does not record sufficient information to recover these changes if media failure occurs after a bulk operation. You can recover a database to the point of failure, but your data may not be consistent if it was changed by a bulk operation. The process of restoration is the same as that of full database recovery.
- Simple recovery model
The simple recovery model does not use the transaction log for recovery. If you use the simple recovery model, you can only restore full database backups. The advantage to using the simple recovery model is that there is less transaction log management. This topic is covered more fully in Simple backup and recovery.
