Sqlserver 还原

--完整还原

RESTORE DATABASE demoData
   FROM DISK=N'D:\Backup\demoData.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']
}