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 codeEXEC sp_adddumpdevice@devtype=’disk’,@logicalname=’MYBackup’,@physicalname=’D:\backup\mydb.bak’删除备份设备的脚本：SQL codeSp_dropdevice@logicalname=’MYBackup’上述脚本只是删除逻辑备份设备的定义，下述脚本将同时删除备份文件：SQL codeSp_dropdevice@logicalname=’MYBackup’,@devfile=’DELFILE’使用逻辑备份设备的方法如下：SQL codeBackup database mydb to MYBackup当然，还可在逻辑备份设备上指定过期时间等备份属性，如：SQL codeBackup database mydb to MYBackup WITHEXPIREDATE=’13/01/2010’或：SQL codeBACKUP DATABASE mydb to MYBackup WITHRETAINDAYS=73、备份集与存储集每一份备份包含于一个备份集，而一个备份集包含于一个存储集。

通过系统GUI进行备份时，SQL Server会自动指定备份集和存储集，目的则是为了简化管理。

用T-SQL显示指定则用如下语法：SQL codeBACKUP DATABASE mydb to MYBackup WITHRETAINDAYS=7,NAME=’FULL’,MEDIANAME=’ALLBackups’NAMEs是指备份集名称，MEDIANAME是指存储集名称。

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

简单的全备份脚本如下所示，也可以通过维护计划来指定全备份：SQL codeBACKUP DATABASE mydb toDISK=’D:\Backup\mydb.bak’但需要注意的是，上述命令是将数据库备份附加到当前的存在的文件上，如果不存在则创建它，并不会覆盖原有文件。

要覆盖同名的备份文件，需要指定INIT参数。

SQL codeBACKUP DATABASE mydb toDISK=’D:\Backup\mydb.bak’ WITH INIT 5、日志备份在完全恢复模式或者大容量日志恢复模式下，日志备份不仅仅是恢复的需要，同时也是手工管理事务日志文件的一种方式。

如果从不进行备份的话，在完全恢复模式或者大容量恢复模式下，事务日志将会持续增长，直至消耗完所在磁盘。

日志备份的脚本如下：SQL codeBACKUP LOG mydb_log TODISK=’D:\backup\mydb.trn’需要养成使用.trn为日志备份的扩展名的习惯。

每个在数据库上的动作都会被安排一个Log Sequence Number (LS N)。

如果需要还原到指定的时间点，需要有持续的LS N记录。

也就是说，在完全恢复模式或者大容量日志模式下，一个不被打断的事务日志备份链是恢复数据库的基本要求。

6、差异备份使用日志备份来恢复时，无疑是一个很慢的过程，特别是上一个全备份的历史比较悠久时。

使用差异备份，便能缩短恢复时间。

事实上，差异备份只是BACKUP DATABASE的一个选项，如下：SQL codeBACKUP DATABASE mydb TODISK=’D:\backup\mydb.dif’ WITHDIFFERENTIAL,INIT进行数据库恢复时，先恢复数据库全备份，再恢复数据库差异备份，最后才恢复日志备份。

差异备份是与上一次全备份紧密相连的，不管期间有多少次日志备份和差异备份，差异备份还是会从上一次全备开始备份。

因此，经常会遇到这样的一种情况，在生产库上需要临时使用数据库时，便用BACKUP DATABASE … TO DIS K=’..’进行了一个备份，下一次的差异备份便会以这回的全备为准，如果过后把这个临时全备删除掉后，后面的差异备份就没用了。

差异备份并不意味着磁盘空间肯定会少，这取决于实际情况。

当期间大量操作发生时，差异备份还是会变得很大。

7、错误检测在备份过程中，备份进程会同时验证数据，或者校验不完整页（torn page），或者验证校验和(checksum)。

要使用该功能，需要激活该选项。

不完整页检测(Torn-page dection)仅仅检查每一个页看是否已经写完成。

如果发现一个页只有部分被写入，那么就将其标记为torn。

校验和验证(checksum validation)是一种新的页验证机制。

它会为每个页添加一个值来表明该页实际的大小。

虽然看起来是个代价很高影响性能的操作，但事实上，它的效率非常高，与torn-page 差不多。