RESTORE Database

Restore Files, Filegroups or Pages

Syntax
      RESTORE DATABASE {database | @database_var} 
         file_or_filegroup_or_pages [ ,...f ]
            [FROM backup_device [ ,...n ] ]
               [WITH 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 ]  

   backup_device:
      logical_backup_device
      @logical_backup_device_var
      {DISK | TAPE } = 'physical_backup_device'
      {DISK | TAPE } = @physical_backup_device_var

   file_or_filegroup_or_pages: 
FILE = {logical_file_name_in_backup | @logical_file_name_in_backup_var}
FILEGROUP = {logical_filegroup | @logical_filegroup_var}
PAGE = 'fileID:pageID [ ,...p ]'

Key:
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 (text string)
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.

FILE - Logical filename in backup (number)
FILEGROUP - Logical filegroup
PAGE - Page restore (full recovery only)

Examples

RESTORE DATABASE MyDatabase
FILE = 'Mydata_1',
FILE = 'Mydata_2',
FILEGROUP = 'Myfilegroup'
FROM MyBackups
GO

"It's a very sobering feeling to be up in space and realize that one's safety factor was determined by the lowest bidder on a government contract" ~ Alan Shepherd

Related commands

Complete Restore - an Entire Database from a Full database backup.
Partial Restore
- part of database/point in time
LOG Restore
- a Transaction Log
RESTORE DATABASE_SNAPSHOT
RESTORE FILELISTONLY - List database and log files
RESTORE VERIFYONLY - Verify the backup but don't restore it
BACKUP
Equivalent Oracle command: RMAN - Recovery Manager


 
Copyright © 1999-2024 SS64.com
Some rights reserved