sql server误update如何回滚 sql server怎么回滚数据

--完整还原

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']   
   }