--完整还原
RESTORE DATABASE demoData FROM DISK=N'D:BackupdemoData.bak';
RESTORE DATABASE { database_name | @database_name_var }
[ FROM <backup_device> [ ,...n ] ]
[ WITH
{
[ RECOVERY --指定还原操作将所有未提交的事务回滚，并使数据库可用；如果后续需要从差异备份和事务日志备份继续还原操作，那么必须使用 NORECOVERY选项；RECOVERY 选项用于还原操作的最后一个Restore命令中。
| NORECOVERY --指定还原操作不回滚未提交的事务，后续需要从差异备份或事务日志备份继续还原操作，在还原过程的最后一个Restore命令之前，使用NoRecovery选项。
| STANDBY =
{standby_file_name | @standby_file_name_var } --RECOVERY（默认值）表示在当前备份完成前滚后，应执行回滚
]
| , <general_WITH_options> [ ,...n ]
| , <replication_WITH_option>
| , <change_data_capture_WITH_option>
| , <FILESTREAM_WITH_option>
| , <service_broker_WITH options>
| , <point_in_time_WITH_options—RESTORE_DATABASE>
} [ ,...n ]
]
[;]
--恢复数据库的一部分（部分还原）
RESTORE DATABASE { database_name | @database_name_var }
<files_or_filegroups> [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
WITH
PARTIAL, NORECOVERY
[ , <general_WITH_options> [ ,...n ]
| , <point_in_time_WITH_options—RESTORE_DATABASE>
] [ ,...n ]
[;]
--将特定文件或文件组还原到数据库（文件还原）
RESTORE DATABASE { database_name | @database_name_var }
<file_or_filegroup> [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
WITH
{
[ RECOVERY | NORECOVERY ]
[ , <general_WITH_options> [ ,...n ] ]
} [ ,...n ]
[;]
--将特定页面还原到数据库（页面还原）
RESTORE DATABASE { database_name | @database_name_var }
PAGE = 'file:page [ ,...n ]'
[ , <file_or_filegroups> ] [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
WITH
NORECOVERY
[ , <general_WITH_options> [ ,...n ] ]
[;]
--将事务日志还原到数据库（事务日志还原）
RESTORE LOG { database_name | @database_name_var }
[ <file_or_filegroup_or_pages> [ ,...n ] ]
[ FROM <backup_device> [ ,...n ] ]
[ WITH
{
[ RECOVERY | NORECOVERY | STANDBY =
{standby_file_name | @standby_file_name_var }
]
| , <general_WITH_options> [ ,...n ]
| , <replication_WITH_option>
| , <point_in_time_WITH_options—RESTORE_LOG>
} [ ,...n ]
]
[;]
--将数据库还原到数据库快照捕获的时间点
RESTORE DATABASE { database_name | @database_name_var }
FROM DATABASE_SNAPSHOT = database_snapshot_name
<backup_device>::= --备份设备定义
{
{ logical_backup_device_name |
@logical_backup_device_name_var }
| { DISK | TAPE | URL } = { 'physical_backup_device_name' |
@physical_backup_device_name_var }
}
<files_or_filegroups>::= --文件或文件组定义
{
FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }
| FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
| READ_WRITE_FILEGROUPS --通过指定READ_WRITE_FILEGROUPS 创建的备份称为“部分备份”。在简单恢复模式下，只允许对只读文件组执行文件组备份。还原的数据备份类型：数据库备份、部分备份或文件备份。对于数据库备份或部分备份，日志备份序列必须从数据库备份或部分备份的结尾处开始延续。对于一组文件备份，日志备份序列必须从整组文件备份的开头开始延续。
}
<general_WITH_options> [ ,...n ]::= --普通WITH选项定义
--还原操作选项
MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'
[ ,...n ]
| REPLACE --在SQL Server实例中，如果要还原的数据和现存的数据库同名，那么，指定Replace选项，SQL Server将会把已存在的同名数据库删除。如果没有指定Replace选项，SQL Server会做安全检查，不会将现存的同名数据库删除
| RESTART --在数据库还原操作中断（interrupt）时，重启还原操作，从中断处重新开始还原操作
| RESTRICTED_USER | CREDENTIAL --对新还原的数据库，限制（restrict）用户访问，只允许角色 db_owner, dbcreator 或 sysadmin 的成员的访问
--备份设置选项
| FILE = { backup_set_file_number | @backup_set_file_number }
| PASSWORD = { password | @password_variable }
--媒体设置选项
| MEDIANAME = { media_name | @media_name_variable }
| MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
| BLOCKSIZE = { blocksize | @blocksize_variable }
--数据事务选项
| BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
--错误检测选项
| { CHECKSUM | NO_CHECKSUM } --检测校验和
| { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }
--检测选项
| STATS [ = percentage ]
--磁带选项
| { REWIND | NOREWIND }
| { UNLOAD | NOUNLOAD }
<replication_WITH_option>::=
| KEEP_REPLICATION
<change_data_capture_WITH_option>::=
| KEEP_CDC
<FILESTREAM_WITH_option>::=
| FILESTREAM ( DIRECTORY_NAME = directory_name )
<service_broker_WITH_options>::=
| ENABLE_BROKER
| ERROR_BROKER_CONVERSATIONS
| NEW_BROKER
<point_in_time_WITH_options—RESTORE_DATABASE>::=
| {
STOPAT = { 'datetime'| @datetime_var }
| STOPATMARK = 'lsn:lsn_number'
[ AFTER 'datetime']
| STOPBEFOREMARK = 'lsn:lsn_number'
[ AFTER 'datetime']
}
<point_in_time_WITH_options—RESTORE_LOG>::=
| {
STOPAT = { 'datetime'| @datetime_var }
| STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime']
| STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime']
}