SqlServer 备份和恢复(一)

SqlServer 备份和恢复

use chenjch 
select * into t1 from sys.objects;
select COUNT(*) from t1; ---54

---数据库全备
BACKUP DATABASE chenjch
TO DISK='F:\backup\sqlserver\chenjch_full.bak'
GO

已为数据库 'chenjch'，文件 'chenjch' (位于文件 1 上)处理了 192 页。
已为数据库 'chenjch'，文件 'chenjch_log' (位于文件 1 上)处理了 6 页。
BACKUP DATABASE 成功处理了 198 页，花费 0.175 秒(8.816 MB/秒)。

---数据库恢复
insert into t1 select * from t1;
select COUNT(*) from t1; ---108

restore filelistonly from disk='F:\backup\sqlserver\chenjch_full.bak';

RESTORE DATABASE chenjch_0617
  FROM DISK = 'F:\backup\sqlserver\chenjch_full.bak' 
  WITH 
  MOVE 'chenjch' TO 'F:\backup\sqlserver\data\chenjch_0617.mdf',   
  MOVE 'chenjch_log' TO 'F:\backup\sqlserver\data\chenjch_0617_log.LDF';
  
已为数据库 'chenjch_0617'，文件 'chenjch' (位于文件 1 上)处理了 192 页。
已为数据库 'chenjch_0617'，文件 'chenjch_log' (位于文件 1 上)处理了 6 页。
RESTORE DATABASE 成功处理了 198 页，花费 27.674 秒(0.055 MB/秒)。

use chenjch_0617 
select COUNT(*) from t1; ---54
只通过全备恢复数据库，默认恢复到备份时刻的数据；

---数据库日志备份
BACKUP LOG chenjch TO DISK='F:\backup\sqlserver\chenjch_log.bak';

已为数据库 'chenjch'，文件 'chenjch_log' (位于文件 1 上)处理了 22 页。
BACKUP LOG 成功处理了 22 页，花费 0.085 秒(2.022 MB/秒)。

---数据库+日志恢复
RESTORE DATABASE chenjch_0617_001
  FROM DISK = 'F:\backup\sqlserver\chenjch_full.bak' 
  WITH NORECOVERY,
  MOVE 'chenjch' TO 'F:\backup\sqlserver\data\chenjch_0617_001.mdf',   
  MOVE 'chenjch_log' TO 'F:\backup\sqlserver\data\chenjch_0617_001_log.LDF';
RESTORE LOG chenjch_0617_001 from disk='F:\backup\sqlserver\chenjch_log.bak' 
  WITH RECOVERY;

已为数据库 'chenjch_0617_001'，文件 'chenjch' (位于文件 1 上)处理了 192 页。
已为数据库 'chenjch_0617_001'，文件 'chenjch_log' (位于文件 1 上)处理了 6 页。
RESTORE DATABASE 成功处理了 198 页，花费 27.525 秒(0.056 MB/秒)。
已为数据库 'chenjch_0617_001'，文件 'chenjch' (位于文件 1 上)处理了 0 页。
已为数据库 'chenjch_0617_001'，文件 'chenjch_log' (位于文件 1 上)处理了 22 页。
RESTORE LOG 成功处理了 22 页，花费 0.085 秒(2.022 MB/秒)。

select COUNT(*) from t1; ---108 
数据库全备+日志 恢复，可以将数据库恢复到最新状态；

---基于时间点恢复
use chenjch
select * into t1 from sys.objects;
select COUNT(*) from t1; ---54

BACKUP DATABASE chenjch
TO DISK='F:\backup\sqlserver\chenjch_full001d.bak'
GO

已为数据库 'chenjch'，文件 'chenjch' (位于文件 1 上)处理了 192 页。
已为数据库 'chenjch'，文件 'chenjch_log' (位于文件 1 上)处理了 2 页。
BACKUP DATABASE 成功处理了 194 页，花费 0.174 秒(8.688 MB/秒)。

---第一次删除数据
delete t1 where TYPE='S';
--保存删除表的时间   
SELECT dt=GETDATE() INTO a;  
select * from a;  ---2018-06-18 14:17:56.387
select COUNT(*) from t1; ---9

---第二次删除数据
delete t1;
--保存删除表的时间   
SELECT dt=GETDATE() INTO b;  
select * from b;  ---2018-06-18 14:21:53.940
select COUNT(*) from t1; ---0

测试将数据恢复到第一次删除数据的时刻；
BACKUP LOG chenjch TO DISK='F:\backup\sqlserver\chenjch_log001d.bak';

已为数据库 'chenjch'，文件 'chenjch_log' (位于文件 1 上)处理了 18 页。
BACKUP LOG 成功处理了 18 页，花费 0.036 秒(3.797 MB/秒)。

use master
RESTORE DATABASE chenjch FROM DISK='F:\backup\sqlserver\chenjch_full001c.bak'
WITH REPLACE,NORECOVERY;

已为数据库 'chenjch'，文件 'chenjch' (位于文件 1 上)处理了 192 页。
已为数据库 'chenjch'，文件 'chenjch_log' (位于文件 1 上)处理了 2 页。
RESTORE DATABASE 成功处理了 194 页，花费 27.487 秒(0.054 MB/秒)。

RESTORE LOG chenjch FROM DISK='F:\backup\sqlserver\chenjch_log001d.bak' 
WITH RECOVERY,STOPAT='2018-06-18 14:18:00'; 

已为数据库 'chenjch'，文件 'chenjch' (位于文件 1 上)处理了 0 页。
已为数据库 'chenjch'，文件 'chenjch_log' (位于文件 1 上)处理了 18 页。
RESTORE LOG 成功处理了 18 页，花费 0.086 秒(1.589 MB/秒)。

use chenjch
select count(*) from t1; ---9 SQLSERVER WITH选项如下：
https://docs.microsoft.com/zh-cn/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

WITH 选项
一：指定要用于备份操作的选项。
(1)CREDENTIAL
适用范围： SQL Server（ SQL Server 2012 (11.x) SP1 CU2 到 SQL Server 2017）和 SQL 数据库托管实例。
仅在创建到 Windows Azure Blob 存储服务的备份时使用。

(2)DIFFERENTIAL
适用范围： SQL Server。
只能与 BACKUP DATABASE 一起使用，指定数据库备份或文件备份应该只包含上次完整备份后更改的数据库或文件部分。 
差异备份一般会比完整备份占用更少的空间。 
对于上一次完整备份后执行的所有单个日志备份，使用该选项可以不必再进行备份。
默认情况下，BACKUP DATABASE 创建完整备份。

(3)ENCRYPTION
用于指定将备份加密。 
可指定加密备份所用的加密算法，或指定 NO_ENCRYPTION 以不加密备份。 
建议进行加密以帮助保护备份文件的安全。 
可指定的算法的列表如下：
AES_128,AES_192,AES_256,TRIPLE_DES_3KEY,NO_ENCRYPTION

二：备份集选项
这些选项对此备份操作创建的备份集进行操作。
(1)COPY_ONLY 
适用范围：
SQL Server 和 SQL 数据库托管实例。
指定备份为“仅复制备份”，该备份不影响正常的备份顺序。 
仅复制备份是独立于定期计划的常规备份而创建的。 
仅复制备份不会影响数据库的总体备份和还原过程。
应在出于特殊目的而进行备份的情况下使用仅复制备份，例如在进行联机文件还原前备份日志。 
通常，仅复制日志备份仅使用一次即被删除。
与 BACKUP DATABASE 一起使用时，COPY_ONLY 选项创建的完整备份不能用作差异基准。 
差异位图不会被更新，因此差异备份的表现就像仅复制备份不存在一样。 
后续差异备份将最新的常规完整备份用作它们的基准。
如果将 DIFFERENTIAL 和 COPY_ONLY 一起使用，则忽略 COPY_ONLY 并创建差异备份。
与 BACKUP LOG 一起使用时，COPY_ONLY 选项将创建“仅复制日志备份”，该备份不会截断事务日志。 
仅复制日志备份对日志链没有任何影响，因此其他日志备份的表现就像仅复制备份不存在一样。

(2){ COMPRESSION | NO_COMPRESSION }
仅适用于 SQL Server 2008 Enterprise 和更高版本；
指定是否对此备份执行备份压缩，覆盖服务器级默认设置。

安装时，默认行为是不进行备份压缩。
但此默认设置可通过设置 backup compression default 服务器配置选项进行更改。 
有关查看此选项的当前值的信息，请参阅查看或更改服务器属性面板 (SQL Server)。

COMPRESSION
显式启用备份压缩。
NO_COMPRESSION
显式禁用备份压缩。

(3)DESCRIPTION = { 'text' | @text_variable }*
指定说明备份集的自由格式文本。 该字符串最长可达 255 个字符。

(4)NAME = { backup_set_name | @backup_set_var }
指定备份集的名称。 名称最长可达 128 个字符。 如果未指定 NAME，它将为空。

(5){ EXPIREDATE ='date' | RETAINDAYS = 天数 }
指定允许覆盖该备份的备份集的日期。 
如果同时使用这两个选项，RETAINDAYS 的优先级别将高于 EXPIREDATE。
如果这两个选项均未指定，则过期日期由 mediaretention 配置设置确定。 
有关详细信息，请参阅 服务器配置选项 (SQL Server)版本的组合自动配置的最大工作线程数。

(6)RETAINDAYS = { days | @days_var } 
指定必须经过多少天才可以覆盖该备份媒体集。如果作为变量 (@days_var) 提供，则必须指定为整数。

三：媒体集选项
这些选项作为一个整体对介质集进行操作。
(1){ NOINIT | INIT }
控制备份操作是追加到还是覆盖备份介质中的现有备份集。 默认为追加到介质中最新的备份集 (NOINIT)。
NOINIT
表示备份集将追加到指定的介质集上，以保留现有的备份集。 如果为介质集定义了介质密码，则必须提供密码。 NOINIT 是默认设置。
INIT
指定应覆盖所有备份集，但是保留介质标头。 
如果指定了 INIT，将覆盖该设备上所有现有的备份集（如果条件允许）。 
默认情况下，BACKUP 将检查下列条件，如果其中的任一条件存在，都不会覆盖备份介质：
所有备份集都未过期。 有关详细信息，请参阅 EXPIREDATE 和 RETAINDAYS 选项
如果 BACKUP 语句给出了备份集名，则该备份集名与备份介质上的名称不匹配。 有关详细信息，请参阅本部分前面介绍的 NAME 选项。

(2){ NOSKIP | SKIP }
控制备份操作是否在覆盖介质中的备份集之前检查它们的过期日期和时间。
NOSKIP
指示 BACKUP 语句在可以覆盖介质上的所有备份集之前先检查它们的过期日期。 这是默认行为。
SKIP
禁用备份集的过期和名称检查，这些检查一般由 BACKUP 语句执行以防覆盖备份集。

(3){ NOFORMAT | FORMAT }
指定是否应该在用于此备份操作的卷上写入介质标头，以覆盖任何现有的介质标头和备份集。
NOFORMAT
指定备份操作在用于此备份操作的介质卷上保留现的有介质标头和备份集。 这是默认行为。
FORMAT
指定创建新的介质集。 FORMAT 将使备份操作在用于备份操作的所有介质卷上写入新的介质标头。 
卷的现有内容将变为无效，因为覆盖了任何现有的介质标头和备份集。
重要
请谨慎使用 FORMAT。 格式化介质集的任何一个卷都将使整个介质集不可用。 例如，如果初始化现有条带介质集中的单个磁带，则整个介质集都将变得不可用。
指定 FORMAT 即表示 SKIP；SKIP 无需显式声明。

(4)MEDIADESCRIPTION = { text | @text_variable }
指定介质集的自由格式文本说明，最多为 255 个字符。

(5)MEDIANAME = { media_name | @media_name_variable }
指定整个备份介质集的介质名称。 
介质名称的长度不能多于 128 个字符，如果指定了 MEDIANAME，则该名称必须匹配备份卷上已存在的先前指定的介质名称。 
如果未指定该选项或指定了 SKIP 选项，将不会对介质名称进行验证检查。

(6)BLOCKSIZE = { blocksize | @blocksize_variable }
用字节数来指定物理块的大小。 
支持的大小是 512、1024、2048、4096、8192、16384、32768 和 65536 (64 KB) 字节。 
对于磁带设备默认为 65536，其他情况为 512。 
通常，由于 BACKUP 自动选择适合于设备的块大小，因此不需要此选项。 显式声明块大小将覆盖自动选择块大小。

如果要建立一个计划在 CD-ROM 上进行复制和还原的备份，请指定 BLOCKSIZE=2048。
通常，只有写入磁带设备时，此选项才会影响性能。

四：数据传输选项
BUFFERCOUNT = { buffercount | @buffercount_variable }
指定用于备份操作的 I/O 缓冲区总数。 可以指定任何正整数；但是，较大的缓冲区数可能导致由于 Sqlservr.exe 进程中的虚拟地址空间不足而发生“内存不足”错误。
缓冲区使用的总计空间由以下内容确定：buffercount/maxtransfersize

(2)MAXTRANSFERSIZE = { maxtransfersize | @* maxtransfersize_variable* } 
指定要在 SQL Server 和备份介质之间使用的最大传输单元（字节）。 
可能的值是 65536 字节 (64 KB) 的倍数，最多可到 4194304 字节 (4 MB)。

五：错误管理选项
使用这些选项可以确定是否为备份操作启用了备份校验和，以及备份操作是否在遇到错误时停止。
(1){ NO_CHECKSUM | CHECKSUM }
控制是否启用备份校验和。
NO_CHECKSUM
显式禁用备份校验和的生成（以及页校验和的验证）。 这是默认行为。
CHECKSUM
如果此选项已启用并且可用，则指定备份操作将验证每页的校验和及页残缺，并生成整个备份的校验和。
使用备份校验和可能会影响工作负荷以及备份吞吐量。

(3){ STOP_ON_ERROR | CONTINUE_AFTER_ERROR }
控制备份操作在遇到页校验和错误后是停止还是继续。
STOP_ON_ERROR
如果未验证页校验和，则指示 BACKUP 失败。 这是默认行为。
CONTINUE_AFTER_ERROR
指示 BACKUP 继续执行，不管是否遇到无效校验和或页撕裂之类的错误。
数据库损坏时，如果无法使用 NO_TRUNCATE 选项备份日志尾部，则可以通过指定 CONTINUE_AFTER_ERROR 而不是 NO_TRUNCATE 尝试执行尾日志备份。

六：兼容性选项
RESTART
从 SQL Server 2008 开始不起作用。 此版本接受该选项，以便与旧版本的 SQL Server 保持兼容。

七：监视选项
STATS [ = percentage ]
每当另一个百分比完成时显示一条消息，并用于测量进度。 如果省略百分比，则 SQL Server 在每完成 10% 就显示一条消息。
STATS 选项报告截止报告下一个间隔的阈值时的完成百分比。 
这是指定百分比的近似值；例如，当 STATS=10 时，如果完成进度为 40%，则该选项可能显示 43%。 
对于较大的备份集，这不是问题，因为完成百分比在已完成的 I/O 调用之间变化非常缓慢。

八：磁带选项
适用范围：SQL Server
这些选项只用于 TAPE 设备。 如果使用的是非磁带设备，则会忽略这些选项。
{ REWIND | NOREWIND }
REWIND 适用范围：SQL Server。指定 SQL Server 释放和倒带磁带。 REWIND 是默认设置。
NOREWIND 适用范围：SQL Server。
指定 SQL Server 在备份操作之后让磁带一直处于打开状态。 在对磁带执行多个备份操作时，可以使用此选项来帮助改进性能。
NOREWIND 包含 NOUNLOAD，并且这些选项在单个 BACKUP 语句中不兼容。

{ UNLOAD | NOUNLOAD }
适用范围：SQL Server
UNLOAD 适用范围：SQL Server
指定在备份完成后自动重绕并卸载磁带。 会话开始时 UNLOAD 是默认值。
NOUNLOAD 适用范围：SQL Server，指定在 BACKUP 操作之后磁带继续在磁带机中加载。

九：特定于日志的选项
适用范围：SQL Server
这些选项仅与 BACKUP LOG 一起使用。
(1){ NORECOVERY | STANDBY = undo_file_name }
NORECOVERY 适用范围：SQL Server
备份日志的尾部并使数据库处于 RESTORING 状态。 当将故障转移到辅助数据库或在执行 RESTORE 操作前保存日志尾部时，NORECOVERY 很有用。
若要执行最大程度的日志备份（跳过日志截断）并自动将数据库置于 RESTORING 状态，请同时使用 NO_TRUNCATE 和 NORECOVERY 选项。

(2)STANDBY = standby_file_name
适用范围：SQL Server*******
备份日志的尾部并使数据库处于只读和 STANDBY 状态。 
将 STANDBY 子句写入备用数据（执行回滚，但需带进一步还原选项）。 
使用 STANDBY 选项等同于 BACKUP LOG WITH NORECOVERY 后跟 RESTORE WITH STANDBY。

(4)NO_TRUNCATE
适用范围：SQL Server
指定不截断日志，并使 数据库引擎 尝试执行备份，而不考虑数据库的状态。 
因此，使用 NO_TRUNCATE 执行的备份可能具有不完整的元数据。 该选项允许在数据库损坏时备份日志。
NO_TRUNCATE
适用范围：SQL Server
指定不截断日志，并使 数据库引擎 尝试执行备份，而不考虑数据库的状态。 
因此，使用 NO_TRUNCATE 执行的备份可能具有不完整的元数据。 该选项允许在数据库损坏时备份日志。