573
Chapter 21: Backup and Recovery Planning
21
■ (^) The Restrict Access to the Restored Database option restricts access to the restored
database only to the members of db_owner, dbcreator, or sysadmin.
■ (^) Because it is possible that the database is restored to a different fi le location than
the original backup, the Restore the Database Files As section in the Files page
includes a way to assign new fi le locations.
■ There are three Recovery state options to choose from when restoring a database:
■ (^) Use the RESTORE WITH RECOVERY option to restore the fi nal backup. This option
recovers the database and does not allow additional transaction logs to be
restored.
■ The RESTORE WITH NORECOVERY option leaves the database non-operational and
enables you to restore additional backups. If you select this option, the Preserve
Replication Settings option is unavailable.
■ (^) The RESTORE WITH STANDBY option leaves the database in a standby mode in
which the database is available for limited read-only access.
If only certain fi les or fi legroups are restored, select Tasks ➪ Restore ➪ File or Filegroups to
select the fi les or fi legroups you want to restore.
Restoring Individual Pages
The ability to restore pages has been available since SQL Server 2005; however, up until
now, you needed to use T-SQL code. In this release you can perform this type of restore
through a native interface inside Management Studio.
What exactly does restoring a page allow you to do? You can restore one or more damaged
pages without having to restore the entire database. Database pages that are suspect
are specifi cally identifi ed in the dbo.suspect_pages table inside the msdb system
database.
To restore a page, select the database you want to restore the page to. From the context or
Action menu, select Tasks ➪ Restore ➪ Page to open the SQL Server Restore page form. The
Restore page is shown in Figure 21-9.
In the section marked Pages, SQL Server lists the pages identifi ed as suspect in your
selected database. If you want to run another check to make sure the suspected pages
list is up to date, click the Check Database Pages button. This initiates a
DBCC CHECKDB WITH PHYSICAL_ONLY check and then re-queries the msdb.dbo
.suspect_pages table for any additional rows pertaining to your database.
In the Tail Log section, specify the location where you want the tail-log backup of the
database stored. This is required because the restore process automatically initiates a tail-
log backup. The default location selected is the default backup location for SQL Server. If
you want to change this, click the Ellipsis button, and specify the new location.
c21.indd 573c21.indd 573 7/31/2012 9:22:53 AM7/31/2012 9:22:53 AM
http://www.it-ebooks.info