Question

SQL如何备份大容量数据库

Answer 1

数据库备份介绍

1、备份位置

在SQL Server上，有多种备份位置可以选择，如本地磁盘，网络磁盘，远程地址，磁带等。

各种备份位置均有自己的优点和缺点。

2、逻辑备份设备

在SQL Server上，可以通过创建逻辑备份设备来完成备份。使用逻辑备份设备的好处是，当变更备份地址时，不需要更改备份脚本，只需要更改逻辑备份设备的定义即可。

创建逻辑备份设备的脚本如下：
SQL code
EXEC sp_adddumpdevice @devtype=’disk’,@logicalname=’MYBackup’,@physicalname=’D:\backup\mydb.bak’

删除备份设备的脚本：
SQL code
Sp_dropdevice @logicalname=’MYBackup’

上述脚本只是删除逻辑备份设备的定义，下述脚本将同时删除备份文件：
SQL code
Sp_dropdevice @logicalname=’MYBackup’,@devfile=’DELFILE’

使用逻辑备份设备的方法如下：
SQL code
Backup database mydb to MYBackup

当然，还可在逻辑备份设备上指定过期时间等备份属性，如：

SQL code
Backup database mydb to MYBackup WITH EXPIREDATE=’13/01/2010’
或：
SQL code
BACKUP DATABASE mydb to MYBackup WITH RETAINDAYS=7

3、备份集与此睁存储集

每一份备份包含于一个备份集，而一个备份集包含于一个存储集。通过系统GUI进行备份时，SQL Server会自动指定备份集和存储集，目的则是为了简化管理。用T-SQL显示指定则用如下语法：
SQL code
BACKUP DATABASE mydb to MYBackup WITH RETAINDAYS=7,

NAME=’FULL’,

MEDIANAME=’ALLBackups’

NAMEs是指备份集名称，MEDIANAME是指存储集名称。

4、全备份
不管恢复模式是哪一个，所有的备份都必须要有一个全备桥扒蠢份，特别是日志备份敏陪和差异备份，如果没有全备份的话，将无法进行恢复。

简单的全备份脚本如下所示，也可以通过维护计划来指定全备份：
SQL code
BACKUP DATABASE mydb to DISK=’D:\Backup\mydb.bak’

但需要注意的是，上述命令是将数据库备份附加到当前的存在的文件上，如果不存在则创建它，并不会覆盖原有文件。要覆盖同名的备份文件，需要指定INIT参数。

SQL code
BACKUP DATABASE mydb to DISK=’D:\Backup\mydb.bak’ WITH INIT

5、日志备份

在完全恢复模式或者大容量日志恢复模式下，日志备份不仅仅是恢复的需要，同时也是手工管理事务日志文件的一种方式。如果从不进行备份的话，在完全恢复模式或者大容量恢复模式下，事务日志将会持续增长，直至消耗完所在磁盘。

日志备份的脚本如下：
SQL code
BACKUP LOG mydb_log TO DISK=’D:\backup\mydb.trn’

需要养成使用.trn为日志备份的扩展名的习惯。

每个在数据库上的动作都会被安排一个Log Sequence Number (LSN)。如果需要还原到指定的时间点，需要有持续的LSN记录。也就是说，在完全恢复模式或者大容量日志模式下，一个不被打断的事务日志备份链是恢复数据库的基本要求。

6、差异备份
使用日志备份来恢复时，无疑是一个很慢的过程，特别是上一个全备份的历史比较悠久时。使用差异备份，便能缩短恢复时间。事实上，差异备份只是BACKUP DATABASE的一个选项，如下：

SQL code
BACKUP DATABASE mydb TO DISK=’D:\backup\mydb.dif’ WITH DIFFERENTIAL,INIT

进行数据库恢复时，先恢复数据库全备份，再恢复数据库差异备份，最后才恢复日志备份。

差 异备份是与上一次全备份紧密相连的，不管期间有多少次日志备份和差异备份，差异备份还是会从上一次全备开始备份。因此，经常会遇到这样的一种情况，在生产 库上需要临时使用数据库时，便用BACKUP DATABASE … TO DISK=’..’进行了一个备份，下一次的差异备份便会以这回的全备为准，如果过后把这个临时全备删除掉后，后面的差异备份就没用了。

差异备份并不意味着磁盘空间肯定会少，这取决于实际情况。当期间大量操作发生时，差异备份还是会变得很大。
7、错误检测

在备份过程中，备份进程会同时验证数据，或者校验不完整页（torn page），或者验证校验和(checksum)。要使用该功能，需要激活该选项。

不完整页检测(Torn-page dection)仅仅检查每一个页看是否已经写完成。如果发现一个页只有部分被写入，那么就将其标记为torn。

校验和验证(checksum validation)是一种新的页验证机制。它会为每个页添加一个值来表明该页实际的大小。虽然看起来是个代价很高影响性能的操作，但事实上，它的效率非常高，与torn-page差不多。

备份进程在备份数据库时，会通过比较在数据库里的和随着备份页写入硬盘时这两个之间的值来进行验证。但是，这个验证并不是自动完成的，需要显示指定，在GUI页面上是个选项。如果通过T-SQL来备份的话，语句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\data\mydb.bak’ WITH CHECKSUM

如果备份过程中，发现了错误，SQL Server会错误信息写入MSDB上的SUSPECT_PAGE表里面。同时，在默认情况下，备份行为会停止的（STOP_ON_ERROR），以便管理员排查错误。

但 备份过程中的校验和验证还有另外一个选项（CONTINUE_ON_ERROR）,也就是说，如果发现错误，备份过程并不会中断，而是将错误页信息记录在 MSDB..SUSPECT_PAGE上而已。需要注意的是，SUSPECT_PAGE表是有行限制的，最多只能达到1000行，如果达到了的话，备份同 样会失败。

激活校验和验证的话，很明显会影响备份的性能。但还是很有必要的。

8、安全备份

完全备份和日志备份语句还支持使用密码属性，如：

SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’ WITH PASSWORD=’mydb’

所指定的密码是很容易破解的。因此，如果确实需要对某些备份数据进行加密的话，可以将备份存放于加密的文件系统或者其它安全的存储设备上。

同时，SQL Server还提供了对真实列进行加密的功能。该加密功能是工业标准。

9、条带备份

有些情况下，单独一个硬盘无法存储一个完整的数据库备份时，可以将数据库备份分成多个部分存储在不同的磁盘上，这种备份方式成为条带备份。使用条带备份的优点很明确，就是能很好的利用空间，但如果某部分备份丢失或者损坏，那整个备份将无效。

其语句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’,

DISK=’E:\mydb.bak’ WITH INIT,CHECKSUM,

CONTINUE_ON_ERROR

上述D盘和E盘上的备份是不可分割的。

10、镜像备份
与条带备份在多个磁盘上保留同一份备份不同的是，镜像备份是在不同磁盘上保留多份备份。其语句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’

MIRROR TO DISK=’E:\mydb.bak’

WITH INIT,CHECKSUM,CONTINUE_ON_ERROR

在实际情况下，对日志备份采取镜像备份方式会比较合适。

11、COPY-ONLY 备份
在差异备份里曾提到过，差异备份是建立在上一个全备份的基础上的。因此如果在一个事先安排好的备份计划里，如果在全备份和差异备份之间再进行了一次全备份后，其差异备份会被打断，如果把临时全备份删除掉后，就产生了数据丢失。

在SQL SERVER 2005以后，SQL SERVER提供了一个选项copy-only.使用copy-only选项进行的全备份便不会打算原先的备份计划，语句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’

WITH INIT,CHECKSUM,COPY_ONLY

三、文件和文件组备份

在SQL SERVER 7.0以后，SQL Server提供了filegroup的概念。文件组不仅仅提供了一个逻辑的存储地址，还允许将不同的表和索引放在不同的文件组上来提高性能和减少备份时间。

在数据存储概述上，提到了数据库有三类数据文件，一般来说，数据库至少需要两个（.mdf和.ldf）或者更多的文件。SQL Server不仅允许有多个文件存在(.ldf)，还允许多个文件组存在。

一个文件组可以有多个文件，每个文件需要仔细规划好初始大小及增量。

1、默认文件组

在创建对象时，如果未明确指定文件组，那么该对象将会存放在默认的文件组上。在默认情况下，默认的文件组是primary，但由于primary文件组不仅可以包含用户数据，同时还存储着数据库结构等技术信息，因此一般建议添加额外的一个文件组，并将其指定为默认文件组。

SQL Server只能有一个默认的文件组。

修改默认文件组的语句如下：
SQL code
ALTER DATABASE mydb MODIFY FILEGROUP mydb DEFAULT；

2、为对象指定文件组
当创建表或者索引时，不管是用户还是系统都需要将表或索引放在某个文件组上。如果在创建表或索引时指定文件组，那么表或索引将会存储在指定的文件组上，而不是默认的文件组。

其指定语句如下：
SQL code
CREATE TABLE test( [id] int,[notes] text) on mydbdata

也可以通过GUI创建表的页面来指定文件组。

3、将对象迁移至指定文件组
如果需要变更对象的存储位置，最简单的方式则是通过GUI属性页面来进行修改通过该方法，可以直观的看到对象的迁移过程。

同时也可以通过T-SQL来修改，例如：
SQL code
ALTER TABLE test drop constraint PK_test WITH (MOVE TO DATA)

4、备份数据文件
备份数据文件同样可以通过BACKUP DATABASE语句来实现。如下：
SQL code
BACKUP DATABASE mydb FILE=’D:\Data\mydb.ndf’ TO DISK=’E:\Backup\mydbdata.bak’

上述语句相当于数据文件级别的全备份，与数据库级别的备份类似，文件级别上的备份也有差异备份，当然前提是要有相对应的文件全备份。

差异备份的语句如下：
SQL code
BACKUP DATABASE mydb FILE=’D:\Data\mydb.ndf’

WITH DIFFERENTIAL

TO DISK=’E:\Backup\mydbdata_dif.bak’

5、备份文件组
与单独备份文件类似，也可以对文件组进行类似的备份操作。备份文件组的方式也有两种，一种是通过GUI界面指定，一种则是通过T-SQL。

T-SQL的语句如下：
SQL code
BACKUP DATABASE mydb FILEGROUP=’PRIMARY’ TO DISK=’E:\Backup\mydbpri.bak’

6、不完全备份(partial backup)
在文件组备份上，不完全备份其实相当于完全备份，可以通过指定关键字READ_WRITE_FILEGROUPS来实现不完全备份。

语句如下：
SQL code
BACKUP DATABASE mydb READ_WRITE_FILEGROUPS TO DISK=’D:\mydb.bak’

那不完全备份到底是什么意思呢？什么时候需要不完全备份？如果对一个文件组设置了只读，而这只读的文件组又需要进行一次备份，这时，可以不用BACKUP DATABASE语句进行备份，只需要挑个时间停止实例，然后执行不完全备份。