To restore a database from a full database backup, expand the server, choose Databases, right-click the database, and choose Tasks | Restore | Database. The Restore Database dialog box appears (see Figure 16-4). On the General page, select databases to which and from which you want to restore. Then check the backup set that you want to use for your backup process.
Note – If you restore from the log backup, do not forget the sequence of restoring different types of backups. First restore the full database backup. Then restore all corresponding transaction logs in the sequence of their creation.
To select the appropriate restore options, choose the Options page (see Figure 16-5) of the Restore Database dialog box. In the upper part of the window, choose one or more restore types. In the lower part of the window, you can choose one of the three existing options. Choosing the first option, Leave the Database Ready to Use by Rolling Back Uncommitted Transactions, instructs the Database Engine to roll forward any committed transaction and to roll back any uncommitted transaction. After applying this option, the database is in a consistent state and is ready for use. This option is equivalent to the RECOVERY option of the RESTORE DATABASE statement.
Note – Use this option only with the last transaction log to be restored or with a full database restore when no subsequent transaction logs need to be applied.
If you click the second option, Leave the Database Non-operational, and Do Not Roll Back Uncommitted Transactions, the Database Engine does not roll back uncommitted transactions because you will be applying further backups. After you apply this option, the database is unavailable for use, and additional transaction logs should be restored. This option is equivalent to the NORECOVERY option of the RESTORE DATABASE statement.
Note – Use this option with all but the last transaction log to be restored or with a differential database restore.
Choosing the third option, Leave the Database in Read-only Mode, specifies the file (in the Standby File text box) that is subsequently used to roll back the recovery effects. This option is equivalent to the STANDBY option in the RESTORE DATABASE statement.
The process of a database restoration from a differential database backup is equivalent to the process of a restoration from a full database backup. In this case, you have to check Differential Database Backup as the backup type in the Restore Database dialog box. The only difference to restoration with the full database backup is that only the first option in the lower half of the Options page (Leave the Database Ready to Use by Rolling Back Uncommitted Transactions) can be applied to the restoration from a differential database backup.
Note – If you restore from a differential backup, first restore the full database backup before you restore the corresponding differential one. In contrast to transaction log backups, only the latest differential backup is applied, because it includes all changes since the full backup.
To restore a database with a new name, expand Databases, right-click the database, and choose Tasks | Restore | Database. On the General page of the Restore Database dialog box, in the To Database drop-down box enter the name of the database you want to create, and in the From Database drop-down box enter the name of the database whose backup is used.
Recovering to a Mark
The Database Engine allows you to use the transaction log to recover to a specific mark. Log marks correspond to a specific transaction and are inserted only if the transaction commits. This allows the marks to be tied to a particular amount of work and provides the ability to recover to a point that includes or excludes this work.
Note – If a marked transaction spans multiple databases on the same database server, the marks are recorded in the logs of all the affected databases.
The BEGIN TRANSACTION statement supports the WITH MARK clause to insert marks into the logs. Because the name of the mark is the same as its transaction, a transaction name is required. (The description option specifies a textual description of the mark.)
The transaction log records the mark name, description, database, user, date and time information, and the log sequence number (LSN). To allow their reuse, the transaction names are not required to be unique. The date and time information is used along with the name to uniquely identify the mark.
You can use the RESTORE LOG statement (with either the STOPATMARK clause or the STOPBEFOREMARK clause) to specify recovering to a mark. The STOPATMARK clause causes the recovery process to roll forward to the mark and include the transaction that contains the mark. If you specify the STOPBEFOREMARK clause, the recovery process excludes the transaction that contains the mark.
Both clauses just described support AFTER datetime. If this option is omitted, recovery stops at the first mark with the specified name. If the option is specified, recovery stops at the first mark with the specified name exactly at or after datetime.
