Partial Restore - part of database/point in time
Syntax
RESTORE DATABASE {database | @database_var}
files_or_filegroups [ ,...f ]
[FROM backup_device [ ,...n ] ]
[WITH PARTIAL option [,option...]] [;]
Options:
{CHECKSUM | NO_CHECKSUM }
{CONTINUE_AFTER_ERROR | STOP_ON_ERROR }
FILE = { backup_set_file_number | @backup_set_file_number }
MEDIANAME = { media | @media_variable }
MEDIAPASSWORD = { mediapassword | @mediapw_variable }
MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ,...n ]
PASSWORD = { password | @password_variable } ]
NORECOVERY
REPLACE
RESTART
RESTRICTED_USER
{REWIND | NOREWIND }
{UNLOAD | NOUNLOAD }
STATS [ = percentage ]
{ STOPAT = { date_time | @date_time_var }
| STOPATMARK = { 'mark' | 'lsn:lsn_number' } [ AFTER datetime ]
| STOPBEFOREMARK = { 'mark' | 'lsn:lsn_number' } [ AFTER datetime ]
}
backup_device:
logical_backup_device
@logical_backup_device_var
{DISK | TAPE } = 'physical_backup_device'
{DISK | TAPE } = @physical_backup_device_var
files_or_filegroups:
FILE = {logical_file_name_in_backup | @logical_file_name_in_backup_var}
FILEGROUP = {logical_filegroup | @logical_filegroup_var}
READ_WRITE_FILEGROUPS
Key:
PARTIAL - Restore the primary filegroup and any specified secondary filegroup(s). This option implicitly selects the primary filegroup; specifying FILEGROUP = 'PRIMARY' is unnecessary. To restore a secondary filegroup, use the FILE or FILEGROUP options.
DATABASE - The target database.
FROM backup_device - The backup device from which to restore.
CHECKSUM - verify all backup checksums (or fail)
NO_CHECKSUM - Disable validation of checksums
FILE - The backup set to be restored.
MEDIANAME - Specify the name for the media, check for a matching media name on the backup volume.
MOVE - The data or log file (logical name) should be moved by restoring it to the file location specified.
NORECOVERY- Do not roll back any uncommitted transactions. Use if applying another tx log later.
REPLACE - Overwrite any existing database with the same name.
RESTART - Restart a restore operation that has been interrupted.
RESTRICTED_USER - Restrict access to the newly restored db to the db_owner, dbcreator, or sysadmin roles.
UNLOAD - Automatically rewind and unload the backup tape when the backup is finished.
STATS - Display a message each time n percentage completes.
STOPATMARK - Recover to a marked transaction or log sequence number
STOPBEFOREMARK - Recover to a marked transaction or log sequence number
Examples
BACKUP LOG ss64 TO tailLogBackup WITH NORECOVERY, NO_TRUNCATE RESTORE DATABASE ss64 FILEGROUP='Primary' FROM backup1
WITH PARTIAL, NORECOVERY
RESTORE DATABASE ss64 FILEGROUP='MyFileGroup' FROM backup2
WITH NORECOVERY
RESTORE LOG ss64 FROM backup3 WITH NORECOVERY
RESTORE LOG ss64 FROM tailLogBackup WITH RECOVERY
"Wealth heaped on wealth, nor truth nor safety buys,
The dangers gather as the treasures rise"
- Samuel Johnson: The Vanity Of Human Wishes
Related commands:
Complete Restore - an Entire Database from a Full database backup.
Restore Files - Files, Filegroups, or Pages
LOG Restore - a Transaction Log
RESTORE DATABASE_SNAPSHOT
RESTORE FILELISTONLY - List database and log files
RESTORE HEADERONLY - List backup header information
RESTORE VERIFYONLY - Verify the backup but don't restore it
BACKUP
Equivalent Oracle command: RMAN - Recovery Manager