SQL Server的备份

原文:SQL Server的备份

0.参考文献

1.恢复模式

SQL Server 备份和还原操作发生在数据库的恢复模式的上下文中。 恢复模式旨在控制事务日志维护。 “恢复模式”是一种数据库属性，它控制如何记录事务，事务日志是否需要（以及允许）备份，以及可以使用哪些类型的还原操作。 有三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式。通常，数据库使用完整恢复模式或简单恢复模式。可以在执行大容量操作之前切换到大容量日志恢复模式，以补充完整恢复模式。数据库可以随时切换为其他恢复模式。

1.1恢复模式概述

下表概述了这三种恢复模式。

 

恢复模式	说明	工作丢失的风险	能否恢复到时点？
简单(SIMPLE)	无日志备份。 自动回收日志空间以减少空间需求，实际上不再需要管理事务日志空间。 有关简单恢复模式下数据库备份的信息，请参阅完整数据库备份 (SQL Server)。	最新备份之后的更改不受保护。 在发生灾难时，这些更改必须重做。	只能恢复到备份的结尾。 有关详细信息，请参阅完整数据库还原（简单恢复模式）。
完全(FULL)	需要日志备份。 数据文件丢失或损坏不会导致丢失工作。 可以恢复到任意时点（例如应用程序或用户错误之前）。 有关完整恢复模式下数据库备份的信息，请参阅完整数据库备份 (SQL Server)和完整数据库还原（完整恢复模式）。	正常情况下没有。 如果日志尾部损坏，则必须重做自最新日志备份之后所做的更改。	如果备份在接近特定的时点完成，则可以恢复到该时点。 有关使用日志备份还原到故障点的信息，请参阅将 SQL Server 数据库还原到某个时点（完整恢复模式）.  注意 如果有两个或更多必须在逻辑上保持一致的完整恢复模式数据库，则最好执行特殊步骤，以确保这些数据库的可恢复性。 有关详细信息，请参阅包含标记的事务的相关数据库的恢复。
大容量日志(BULK_INSERT)	需要日志备份。 是完整恢复模式的附加模式，允许执行高性能的大容量复制操作。 通过使用最小方式记录大多数大容量操作，减少日志空间使用量。 有关尽量减少日志量的操作的信息，请参阅事务日志 (SQL Server)。 有关大容量日志恢复模式下数据库备份的信息，请参阅完整数据库备份 (SQL Server) 和完整数据库还原（完整恢复模式）。	如果在最新日志备份后发生日志损坏或执行大容量日志记录操作，则必须重做自该上次备份之后所做的更改。 否则不丢失任何工作。	可以恢复到任何备份的结尾。 不支持时点恢复。

1.2.如何查看或更改数据库恢复模式 (SQL Server Management Studio)

查看或更改数据库的恢复模式

1. 连接到相应的 Microsoft SQL Server 数据库引擎 实例之后，在对象资源管理器中，单击服务器名称以展开服务器树。
2. 展开“数据库”，然后根据数据库的不同，选择用户数据库，或展开“系统数据库”，再选择系统数据库。
3. 右键单击该数据库，再单击“属性”，这将打开“数据库属性”对话框。
4. 在“选择页”窗格中，单击“选项”。
5. 当前恢复模式显示在“恢复模式”列表框中。
6. 也可以从列表中选择不同的模式来更改恢复模式。可以选择“完整”、“大容量日志”或“简单”。如下图所示：

7. 

1.3.使用TSQL更改数据库恢复模式

--更改数据库恢复模式
alter database AdventureWorks2012 set recovery {FULL|SIMPLE|BULK_LOGGED}
--简单恢复模式
alter database AdventureWorks2012 set recovery SIMPLE
--完整恢复模式
alter database AdventureWorks2012 set recovery BULK_LOGGED
--大容量日志恢复模式
alter database AdventureWorks2012 set recovery FULL

1.4.估计完整数据库备份的大小

　　在实现备份与还原策略之前，应当估计完整数据库备份将使用的磁盘空间。 备份操作会将数据库中的数据复制到备份文件。 备份仅包含数据库中的实际数据，而不包含任何未使用的空间。 因此，备份通常小于数据库本身。（这也是为什么完整数据库备份比文件备份更加节省空间的原因。） 您可以使用 sp_spaceused系统存储过程估计完整数据库备份的大小。 有关详细信息，请参阅 sp_spaceused (Transact-SQL)。

2.为磁盘文件定义逻辑备份设备

2.1.限制和局限

逻辑设备名称在服务器实例上的所有逻辑备份设备中必须是唯一的。 若要查看现有逻辑设备名称，请查询sys.backup_devices 目录视图。

2.2.建议

我们建议备份磁盘应不同于数据库数据和日志的磁盘。 这是数据或日志磁盘出现故障时访问备份数据必不可少的。

2.3使用 SQL Server Management Studio为磁盘文件定义逻辑备份设备

1. 连接到相应的 Microsoft SQL Server 数据库引擎实例之后，在对象资源管理器中，单击服务器名称以展开服务器树。
2. 展开“服务器对象”，然后右键单击“备份设备”。
3. 单击“新建备份设备”。 将打开“备份设备”对话框。
4. 输入设备名称。
5. 若要确定目标位置，请单击“文件”并指定该文件的完整路径。
6. 若要定义新设备，请单击“确定”。

若要备份至新设备，右键设备名称，选择"back up a database"，然后再具体的对话框中选择需要备份的数据库。如下图所示：

2.4.使用 Transact-SQL为磁盘文件定义逻辑备份

--查询备份设备
select * from  sys.backup_devices;
--定义磁盘备份设备
EXEC sp_addumpdevice 'disk', 'mybackupdisk', 'd:\backup\backup1.bak' ;
--删除磁盘备份设备
EXEC sp_dropdevice 'mybackupdisk', 'delfile' ;

3.创建完整数据库备份 (SQL Server)

关于完整备份的点（PS：2012-7-17）

问题：sql server从2点开始备份，4点备份完。那么进行完整还原的时候，恢复到的是哪一个时间点。是2点，还是4点，或者是其他时间点。

解答：首先，恢复到的是4点。这是因为在进行full database backup的时候，会有一个开始备份的LSNs，在full database backup 完成的时候，又有一个备份完成的LSNe。在备份完成以后，数据库会redo从LSNa到LSNb这一段log record。所以回复到的是4点。

执行如下命令

dbcc log(TESTDB3,3)
checkpoint
backup database TESTDB3 to disk='d:\backup\backup3.bak'
dbcc log(TESTDB3,3)

查询结果如下所示：

这说明进行backup的时候会被记录到log record中。

3.1.限制和局限

• 不允许在显式或隐式事务中使用 BACKUP 语句。

• 无法在早期版本的 SQL Server 中还原较新版本的 SQL Server 创建的备份。

3.2.建议

• 随着数据库不断增大，完整备份需花费更多时间才能完成，并且需要更多的存储空间。 因此，对于大型数据库而言，您可以用一系列“差异数据库备份”来补充完整数据库备份。 有关详细信息，请参阅差异备份 (SQL Server)。

• 您可以使用 sp_spaceused 系统存储过程估计完整数据库备份的大小。

• 默认情况下，每个成功的备份操作都会在 SQL Server 错误日志和系统事件日志中添加一个条目。 如果非常频繁地备份日志，这些成功消息会迅速累积，从而产生一个巨大的错误日志，这样会使查找其他消息变得非常困难。 在这些情况下，如果任何脚本均不依赖于这些日志条目，则可以使用跟踪标志 3226 取消这些条目。 有关详细信息，请参阅跟踪标志 (Transact-SQL)。

3.3.权限

默认情况下，为 sysadmin 固定服务器角色以及 db_owner 和 db_backupoperator 固定数据库角色的成员授予 BACKUP DATABASE 和 BACKUP LOG 权限。

备份设备的物理文件的所有权和权限问题可能会妨碍备份操作。 SQL Server 必须能够读取和写入设备；运行 SQL Server 服务的帐户必须具有写入权限。 但是，用于在系统表中为备份设备添加项目的 sp_addumpdevice 不检查文件访问权限。 备份设备物理文件的这些问题可能直到为备份或还原而访问物理资源时才会出现。

3.4.使用 SQL Server Management Studio备份数据库

右键数据库AdventureWorks2012->tasks->back up，如下图所示：

完全备份配置选项

1. backup type:full，表示完全备份
2. backup component:database
3. backup set->name:默认
4. backup set will expire：after：0，表示永远不失效。
5. destination：disk，表示备份到磁盘
6. 点击add选择备份设备或者指定备份路径，如下图所示：

7. 

8. 我们这里选择的是前面创建的backup device：mybackupdisk。
9. 注意：我们可以指定多个backup device。指定多个备份设备可以节约备份时间。并行写入。

验证

完成上述配置以后并确定备份，我们就可以在磁盘目录”d:\backup\backup1.bak"下找到我们的备份文件，一共有189MB。

3.5.使用 Transact-SQL创建完整数据库备份

--默认情况下，BACKUP DATABASE 创建完整备份。
--定义备份设备
EXEC sp_addumpdevice 'disk', 'mybackupdisk2', 'd:\backup\backup2.bak';
--备份到逻辑设备
BACKUP DATABASE AdventureWorks2012 TO mybackupdisk2
   WITH NOINIT,NAME = 'Full Backup of AdventureWorks2012';
--直接备份到磁盘
BACKUP DATABASE AdventureWorks2012 TO disk='d:\backup\backup3.bak'
   WITH NOINIT,NAME = 'Full Backup of AdventureWorks2012';

BACKUP具体语法参考BACKUP (Transact-SQL)。

4.差异数据库备份

4.1.必备条件

　　创建差异数据库备份需要有以前的完整数据库备份。 如果选定的数据库从未进行过备份，则请在创建任何差异备份之前，先执行完整数据库备份。 有关详细信息，请参阅创建完整数据库备份 (SQL Server)。

4.2.建议

　　当差异备份的大小增大时，还原差异备份会显著延长还原数据库所需的时间。 因此，建议按设定的间隔执行新的完整备份，以便为数据建立新的差异基准。 例如，您可以每周执行一次整个数据库的完整备份（即完整数据库备份），然后在该周内执行一系列常规的差异数据库备份。

4.3.使用 SQL Server Management Studio创建差异数据库备份

操作步骤跟3.4节完整备份数据库一样，只是将buckup type类型改成Differential而已。destination可以依然选择完整备份的那一个device，不过要求是NOINIT，而不能是INIT，因为如果是INIT的话会覆盖原来的完整备份。

4.4.使用 Transact-SQL创建差异数据库备份

USE [TSQL2012]
--定义备份设备
EXEC sp_addumpdevice 'disk', 'backupdevice1', 'd:\backup\backup_tsql2012.bak';

--完整备份数据库
BACKUP DATABASE TSQL2012 TO backupdevice1 WITH NOINIT,NAME = 'Full Backup of TSQL2012';

--插入数据
INSERT INTO dbo.test(OrderID,ProductID) VALUES(1,1);
INSERT INTO dbo.test(OrderID,ProductID) VALUES(2,2);
INSERT INTO dbo.test(OrderID,ProductID) VALUES(3,3);

--差异备份数据库
BACKUP DATABASE TSQL2012 TO backupdevice1 WITH DIFFERENTIAL,NOINIT,NAME = 'DIFFERENTIAL Backup of TSQL2012';

验证我们创建的完整备份和差异备份

右键选择数据库TSQL2012->tasks->restore->database，出现如下图所示内容：

如上图所示，出现了我们之前创建的完整备份和差异备份。

5.事务日志备份

5.1.建议

• 如果数据库使用完整恢复模式或大容量日志恢复模式，则必须足够频繁地备份事务日志，以保护数据和避免事务日志变满。 这将截断日志，并且支持将数据库还原到特定时间点。

• 默认情况下，每个成功的备份操作都会在 SQL Server 错误日志和系统事件日志中添加一个条目。 如果非常频繁地备份日志，这些成功消息会迅速累积，从而产生一个巨大的错误日志，这样会使查找其他消息变得非常困难。 在这些情况下，如果任何脚本均不依赖于这些日志条目，则可以使用跟踪标志 3226 取消这些条目。 有关详细信息，请参阅跟踪标志 (Transact-SQL)。

5.2.使用 SQL Server Management Studio创建差异数据库备份

5.2.1.例行事务日志的备份

操作步骤跟3.4节完整备份数据库一样，只是将buckup type类型改成Transaction Log而已。然后选择Option选项，我们重点看一下Transaction Log选项。对于例行的日志备份，请保留默认选项“通过删除不活动的条目截断事务日志(Truncate the transaction log)”。如下图所示：

5.2.2.尾部事务日志的备份

若要备份日志尾部（即活动的日志），请选中“备份日志尾部，并使数据库处于还原状态(Back up the tail of the log, and leave database in the restoring state)”。

5.3.使用 Transact-SQL创建差异数据库备份

--插入数据
INSERT INTO dbo.test(OrderID,ProductID) VALUES(4,4);
INSERT INTO dbo.test(OrderID,ProductID) VALUES(5,5);

--事务日志备份，NOINIT是默认的，表示不重写，而是追加
BACKUP LOG TSQL2012 TO backupdevice1 WITH NAME = 'Transaction Log Backup of TSQL2012';

--插入数据
INSERT INTO dbo.test(OrderID,ProductID) VALUES(6,6);
INSERT INTO dbo.test(OrderID,ProductID) VALUES(7,7);

--结尾日志备份，如果报错说数据库正在使用，请重启服务。
use master
go
BACKUP LOG TSQL2012 TO backupdevice1 WITH NORECOVERY,NAME = 'Transaction Tail-Log Backup of TSQL2012';
go

注意：事务日志备份与结尾日志备份就只差了一个关键字NORECOVERY，数据库备份可以有多个事务日志，但是只有一个结尾日志。一般结尾日志多用在数据库恢复的时候。在数据库恢复中，恢复事务日志并不能说明数据库已经恢复完成，但是一旦恢复了结尾日志，这表明数据库恢复工作完成。

验证我们创建的事务日志与结尾日志

右键选择数据库TSQL2012->tasks->restore->database，出现如下图所示内容：

由上图可见，备份中多出我们上述定义的事务日志：Transaction Log Backup of TSQL2012和结尾日志：Transaction Tail-Log Backup of TSQL2012。