Restore Methods

3 4

As mentioned, the type of backup performed affects the nature of the restore operation. In this section, you'll learn how to perform a restore from a full backup, from a differential backup, and from transaction log backups.

Restoring from a Full Backup

Restoring from a full backup is a fairly straightforward process: you simply restore the backup files by using either SQL Server Enterprise Manager or Transact-SQL (TSQL) commands. Instructions for restoring data by using these two methods are given later in this chapter. If you're planning on restoring from differential backups after you restore from a full backup, be sure you back up the current transaction log, as described in the section "Restoring from the Transaction Log Backups" later in this chapter, and specify the NORECOVERY option when you perform the restore.

NOTE

---

An important feature of the restore operation is the RECOVERY option. The RECOVERY option instructs SQL Server to attempt to recover the database by using the online transaction log after the restore is complete. If you're planning on using differential backup files or transaction log backup files, be sure you specify the NORECOVERY option.

Restoring from a Differential Backup

To restore from a differential backup, you must first restore from a full backup and then restore all of the differential backups that have been created since the last full backup. Remember, a differential backup is used to back up information that has changed since the last full or differential backup. Be sure you use the NORECOVERY option unless you are restoring the most recent backup file, for which you will use the RECOVERY option. If you are restoring from the transaction logs in addition to the differential backup, you must also back up the current log and apply all of the changed log files, as described in the next section.

Restoring from the Transaction Log Backups

To perform a recovery in which the database is returned to the state it was in just before the point of failure, you must first restore the data files from the latest full backup and then restore the changes that have been made to the database since that backup. You restore these changes by restoring all of the transaction log backups that have occurred since the failure.

To ensure that you do not lose any of the newest transactions when you restore the logs, you must first save the current log. If you forget to save the current log, you will lose the most recent changes recorded in the log because the restore operations will overwrite the transaction log.

To use transaction logs to restore the database to the state it was in just prior to failure, follow these general steps, building upon the techniques you learned in Chapter 32:

1. Back up the currently active transaction log, using the NO_TRUNCATE option.
2. Restore the latest full backup.
3. Restore any differential backups to return the database to the state it was in when the latest backup was performed.
4. Restore all transaction log backups created since the last differential backup to replay any transactions that have occurred since that last backup.
5. Restore the transaction log backup you created in step 1 to return the database to the state it was in just prior to the failure.

Restoring a Database in BULK_LOGGED Recovery Mode

If you are running your database in BULK_LOGGED recovery mode, you must redo any minimally logged operations if a restore is necessary. These operations include SELECT…INTO, BULK COPY, BCP, and some CREATE INDEX operations, in addition to the text operations that were mentioned in the previous chapter. If this places an undue burden on you, you should not be running your database in BULK_LOGGED recovery mode.