Overview
SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery or simple model. A database can be switched to another model at any time.
To set the recovery model for a database, do the following:
- Open the Microsoft SQL Server Management Studio
- Connect to the appropriate server with the following screen:
- On the databases treeview,
select your DeMaSy database and right-click on it to display the Properties screen. Select the Options tab:
The recovery model can be changed there.
Database backup/restore under the simple recovery model
- Transaction logs are not backed up.
- Changes since the most recent database backup are unprotected. In the event of a disaster, those changes must be redone by the user.
- Under the simple recovery model, the database cannot be restored to a specific point in time within a specific backup.
- If you are using a full database backup, just restore the most recent backup, as shown in the following illustration:
Database backup/restore under the full recovery model
- This model requires transaction log backups.
- No work is lost due to a lost or damaged data file.
- Can recover to a specific point in time assuming that your backups are complete up to that point in time.
- The following illustration shows the least complex backup strategy that is possible under the full recovery model:
- Under the full recovery model, after you restore your data backup, you must restore all subsequent transaction log backups and then recover the database.
Typically, recovering a database to the point of a failure involves the following basic steps:- Backup the active transaction log (tail-log backup).
- Restore the most recent full database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).
- Starting with the first transaction log backup that was created after the backup you just restored, restore the logs in sequence (RESTORE LOG database_name from log_backup WITH NORECOVERY).
- Recover the database (RESTORE DATABASE database_name WITH RECOVERY).
- The following illustration shows this restore sequence. After a failure occurs (1), a tail-log backup is created (2). Next the database is restored to the point of failure. This involves restoring a database backup, a subsequent differential backup if needed, and every log backup taken after the differential backup (or the full backup if not differential backup is needed), including the tail-log backup.
Bulk-logged recovery model
- The bulk-logged recovery model is designed for intermittent use to improve the performance of bulk (mass) imports of large amounts of data.
- This model requires the log backups.
- Reduces log space usage by using minimal logging for most bulk operations.
- Point-in-time recovery is not supported.
For more information about those recovery models, please navigate with the following links: