575
Chapter 21: Backup and Recovery Planning
21
The backupset table in msdb database contains a row for each backup set. You can query this table to fi nd infor-
mation on all the successful backups.The RESTORE command restores from a full, differential, or transaction-log backup:RESTORE DATABASE (or LOG) DatabaseName
Optional-File or Filegroup or Page
FROM BackUpDevice
WITH
FILE = FileNumber,
PARTIAL,NORECOVERY or RECOVERY or STANDBY = UnDoFileName,
REPLACE,
STOPAT datetime,
STOPATMARK = 'markname'
STOPBEFOREMARK = 'markname'
To restore a full or differential backup, use the RESTORE DATABASE command; otherwise,
use the RESTORE LOG for a transaction log. To restore a specifi c fi le or fi legroup, add its
name after the database name. The PARTIAL option specifi es a partial restore that restores
the primary fi legroup and any specifi ed secondary fi legroups.A backup set often contains several backup instances. For example, a backup set might
consist of the following:1 : Full backup
2 : Differential backup
3, 4, 5, 6: Transaction-log backups
7 : Differential backup
8, 9: Transaction-log backups
The WITH FILE option specifi es the backup to restore. If it’s left out of the command, the
fi rst backup instance is restored.If a password were created with the backup, the password is required to perform restore
from the backup.Avoid using the password option because this feature will be removed in the future release of SQL Server.To restore one or more pages, use PAGE = 'file:page[,. ..n]' where PAGE indicates
a list of one or more fi les and pages; file indicates the fi le ID containing the page to be
restored; page indicates the page id of the page to be restored in the fi le; and n indicates
multiple pages can be specifi ed.c21.indd 575c21.indd 575 7/31/2012 9:22:53 AM7/31/2012 9:22:53 AM
http://www.it-ebooks.info