Share via

Restore Under the Bulk-Logged Recovery Model

  • Article

This topic discusses restore considerations that are unique to bulk-logged recovery model, which is intended exclusively as a supplement to the full recovery model.

Note

For an introduction to the bulk-logged recovery model, see Backup Under the Bulk-Logged Recovery Model.

Generally, the bulk-logged recovery model is similar to the full recovery model, and the information described for the full recovery model also applies to both. However, point-in-time recovery and online restore are affected by the bulk-logged recovery model.

Restrictions for Point-in-time Recovery

If a log backup taken under the bulk-logged recovery model contains bulk-logged changes, point-in-time recovery is not allowed. Trying to perform point-in-time recovery on a log backup that contains bulk changes will cause the restore operation to fail.

Restrictions for Online Restore

An online restore sequence works only if the following conditions are met:

  • All required log backups must have been taken before the restore sequence starts.
  • Bulk changes must be backed before starting the online restore sequence.
  • If bulk changes exist in the database, all files must be either online or defunct. (This means that it is no longer part of the database.)

If these conditions are not met, the online restore sequence fails.

Note

We recommend switching to the full recovery model before starting an online restore. For more information, see Considerations for Switching from the Full or Bulk-Logged Recovery Model.

For information about how to perform an online restore, see Performing Online Restores.

See Also

Concepts

Overview of the Recovery Models
Performing File Restores (Full Recovery Model)
Overview of Restore and Recovery in SQL Server
Choosing the Recovery Model for a Database
Considerations for Switching from the Full or Bulk-Logged Recovery Model
Working with Transaction Log Backups
Minimally Logged Operations

Other Resources

Creating Full and Differential Backups of a SQL Server Database
Restoring a Database to a Point Within a Backup

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • Added the "Restrictions for Point-in-time Recovery" section
Changed content: