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