RESTORE Database

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


© Copyright SS64.com 1999-2014
Some rights reserved