Sqlserver数据库备份与还原

一、数据库存储概述

1、数据文件类型

· Primary data files:每个数据库都有一个单独的主要数据文件，默认以.mdf扩展名。主要数据文件不仅包含数据信息，还包含与该数据库结构相关的信息。创建数据库时，数据库结构相关信息不仅存在于master数据库中，同时还包含在primary data file上.

· Secondary data files一个数据库可以有一个或者多个二级数据文件，默认以.ndf为扩展名。一般来说二级数据文件并不是必须的，因为二级数据文件不包含文件位置等信息。

· Transaction logs: 数据库必须至少有一个事务日志文件，默认以.ldf为扩展名。日志是整个数据库的命脉，事务日志不可读的话，将无法对数据库进行任何操作。

当你在数据库上进行数据操作时，数据并不是直接写入数据文件，而是先将相关操作信息写入事务日志文件。当一个事务结束时，该事务被标记为已提交，但这也并不意味着数据从日志文件写入了数据文件中。一个标记为已提交的事务仅仅意味着所有与该事务相关的元素已经成功完成。The buffer cache may be updated, but not necessarily the data file.

检查点(checkpoint)会周期性的发生。检查点发生时，是确认所有已提交的事务，不管是在buffer cache或者事务日志中，都被写入相关的数据文件中。检查点(checkpoint)可以通过以下方式来触发：

1、显示调用checkpoint命令；

2、Recover Interval 实例设置的周期达到（用来标识多久发生一次checkpoint）

3、做了数据库备份（在简单模式下）；

4、数据库文件结构被改变（在简单模式下）；

5、数据库引擎被结束。

一般来说，写数据的过程是由系统自动完成的，如下图所示，但数据并不是直接写入.mdf或者.ndf 文件中，而是先将有关变化写入事务日志中，这也是数据库中的write-ahead机制。

1. 用户执行insert, update, delete等语句；

2. 数据立即被写入内部日志缓存中(internal log cache)

3. 日志缓存会更新物理事务日志文件，同时将在buffer cache上执行相关变化

4.数据缓存（data buffer）清除所有在缓存上的脏数据，数据文件被更新。

1、恢复模式类型

所有的数据库都可以设置为三个不同的恢复模式：简单(simple)，完全(full)，大容量日志(Bulk-Logged).

A完全恢复模式

完全恢复模式是默认的恢复模式。在完全恢复模式下，需要手工的对事务日志进行管理。使用完全恢复模式的优点是可以恢复到数据库失败或者指定的时间点上。缺点则是，如果没有进行管理的话，事务日志将会快速增长，消耗磁盘空间。要清除事务日志，只能通过备份事务日志，或者切换至简单模式。

如上图所示，在完全恢复模式下，事务日志会持续增长，而不管checkpoint的发生。

B简单恢复模式

与完全恢复模式不同的是，在简单恢复模式下，在检查点发生时（checkpoint），当前已被提交的事务日志将会被清除。

如上图所示，在检查点发生时，所有已提交的事务日志信息将会从事务日志里面删除。

因此，在简单恢复模式下，容易造成数据丢失，因为无法将数据库恢复到失败的那一刻。

需要注意的是，虽然在简单恢复模式下，系统会自动定期清除日志，但这并不意味着事务日志文件不会增长。例如，如果执行一个批量插入操作时，SQL SERVER会将该相关操作当成一个事务，期间产生的日志量在极端情况下，还是非常可观的。

C大容量日志恢复模式

大容量日志恢复模式与完全恢复模式非常相似，但与完全恢复模式不同的是，批量操作将会尽量被最少记录。

批量操作有以下几种类型：

1. 批量导入数据，例如使用BCP(Bulk Copy Import) ，BULK INSERT命令，或者是在BULK 使用OPENROWSET命令；

2. 大对象操作(LOB),例如在TEXT, NTEXT, IMAGE 列上使用WRITETEXT 或者UPDATETEXT；

3. SELECT INTO 字句；

4. CREATE INDEX, ALTER INDEX, ALTER INDEX REBUILD, DBCC REINDEX

在完全恢复模式下，上述操作产生的日志将会是非常大的。而使用大容量日志恢复模式将会阻止不需要或者非预期的日志增长。

在批量操作发生时，SQL SERVER仅仅记录了相关数据页(data page)的ID，在SQL SERVER中，SQL SERVER pages 都有内部ID，如5：547。用这种方式，能够将大量的page ID记录在小的日志文件里。

使用大容量日志恢复模式，将会使数据仓库或者有大批量操作的数据库减少很大的空间。但使用大容量恢复模式时，会使得恢复变得比较困难，一般来说，只能恢复到最后的事务日志备份点上，但如果所有的事务日志都被备份后，还是可以恢复成功的。

只要在必要时才使用大容量恢复模式，而且使用完成后，还需切换至完全恢复模式，同时进行备份。

2、改变恢复模式

改变数据库的恢复模式，可以通过以下语句来实现：

ALTER DATABASE database_name SET RECOVEY BULK_LOGGED

也可以通过GUI界面来修改。

改变恢复模式并不需要重启数据库实例。

二、数据库备份介绍

1、备份位置

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

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

2、逻辑备份设备

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

创建逻辑备份设备的脚本如下：

SQL code

EXEC sp_adddumpdevice

@devtype=’disk’,@logicalname=’MYBac

kup’,@physicalname=’D:\backup\mydb.b

ak’

删除备份设备的脚本：

SQL code

Sp_dropdevice

@logicalname=’MYBackup’

上述脚本只是删除逻辑备份设备的定义，下述脚本将同时删除备份文件：

SQL code

Sp_dropdevice

@logicalname=’MYBackup’,@devfile=’D

ELFILE’

使用逻辑备份设备的方法如下：

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 (LS N)。如果需要还原到指定的时间点，需要有持续的LS N记录。也就是说，在完全恢复模式或者大容量日志模式下，一个不被打断的事务日志备份链是恢复数据库的基本要求。

6、差异备份

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

SQL code

BACKUP DATABASE mydb TO

DISK=’D:\backup\mydb.dif’ WITH

DIFFERENTIAL,INIT

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

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

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

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上的S USPECT_PAGE表里面。

同时，在默认情况下，备份行为会停止的（STOP_ON_ERROR），以便管理员排查错误。

但备份过程中的校验和验证还有另外一个选项（CONTINUE_ON_ERROR）,也就是说，如果发现错误，备份过程并不会中断，而是将错误页信息记录在MSDB..SUSPECT_PAGE上而已。需要注意的是，S USPECT_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语句进行备份，只需要挑个时间停止实例，然后执行不完全备份。

四、数据恢复

1、Restore vs. Recovery

Restore和Recovery是两个不同的概念，但在数据恢复过程中又是紧密联系的。

Restore相当于从备份集中重建整个或者部分数据库，Restore是无法改变数据库状态的，如脱机和联机等。

Recovery 则是将数据库从脱机状态恢复到联机状态中供用户使用。Recovery在SQL Server启动时也会发生，在数据库启动过程中，SQL Server会检查事务日志，看是否存在已提交或未提交的事务，如果发现在最后一次检查点发生后，还有已提交的事务，则SQL Server会对这些事务进行REDO(ROLL FORWARD)；而如果发现未提交的事务，则进行UNDO（ROLL BACK）。

一旦对数据库进行了Recovery，则将无法再进行Restore操作。

Recovery 事实上是Restore的一个选项，默认情况下，进行Restore操作时，SQL Server还会进行Recovery操作。在单独对全备份进行恢复时，可以不用考虑Recovery，但如果后续仍有日志备份或差异备份需要恢复，则必须注意Recovery选项的选择。

数据库恢复的语句如下：

SQL code

RESTORE DATABASE mydb FROM mydbdevice

WITH RECOVERY

显示指定RECOVERY 或者NORECOVERY是个良好的习惯。

2、在RECOVERY过程中的可用性

一般情况下，如果SQL Server处于Recovery过程中时，用户是无法使用数据库的。但可以通过设置fast recovery使得用户在Recovery中使用数据库。

在所有已提交的事务都被ROLL FORWARD后，数据库就是联机状态。

一般情况下，SQL Server强制已提交的事务写入数据库中，这个过程叫做检查点（CHECKPOINT）。可以通过强制使用CHECKPOINT命令来触发，默认则是以RECOVERY INTERVAL为周期，可以通过SP_CONFIGURE命令来设置RECOVERY INTERVAL。

SQL code

sp_configure 'Show Advanced Options',1

sp_configure 'recovery interval',5

RECONFIGURE WITH OVERRIDE

sp_configure 'Show Advanced Options',0

默认情况下，RECOVERY INTERVAL 设置为0，表示SQL Server根据需要动态管理CHECKPOINT 的发生。

3、备份文件里面的信息

在primary文件里，存储着与数据库结构有关的一些信息，如文件位置等。因此备份后，这些信息同样保留在备份文件里面。如果要恢复到不同的磁盘上或服务器后，需要进行额外的更改。

在SQL Server里，提供了RESTORE HEADERONLY, RESTORE FILELISTONLY, RESTORE LABELONLY, RESTORE VERIFYONLY 等命令来读取备份文件的信息。也可以通过MS DB里的表来获取备份集的相关信息。

A．RESTORE HEADERONLY

SQL code