SQL Server 2005&2008备份与恢复 I
一篇旧小结，内容都来自互联网~

一、数据库存储概述
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=’MYBackup’,@physicalname=’D:\backup\mydb.bak’

删除备份设备的脚本：

 

SQL code

 Sp_dropdevice @logicalname=’MYBackup’

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

 

SQL code

                    

                    
                    
                    
                    

      

        
推荐阅读更多>
      
      

          

        
                       

                             去掉重复，取最大日期的数据，用 
                    
                       

                             怎么创建千万级以及更大的数据库 
                    
                       

                             用 tinyint 仍是 char(1)  
                    
                       

                             考了一门SQL 2008，却有四张证书，为什么 
                    
                       

                             在存储过程中,怎么返回值 
                    
                       

                             菜鸟，想请教一上if的用法 
                    
                       

                             用sql语句得到本地ip？解决方法 
                    
                       

                             SSRS报表中用SSAS作为数据源，参数传递有关问题 
                    
                       

                             跪求sql的教科书 
                    
                       

                             SQL 能否如何查出某个表的所有字段并保留原来的顺序 
                    
                       

                             关于sql里触发器有关问题 
                    
                       

                             怎么监测mssql2000返回结果集的字节数 
                    
                       

                             SQL中是备注类型,小弟我想导出到Access 时变成文本类型:text(200) 
                    
                       

                             求条简单的查询语句！解决方案 
                    
                       

                             怎么按某个关键字对一张表进行全文搜索 
                    
                       

                             sql 临时表解决办法 
                    
                       

                             怎么删除重复数据 
                    
                       

                             考验你，这句SQL有点难！该怎么处理 
                    
                       

                             SHOPEX程序，数据库400M左右，导致系统运行缓慢，想优化一下数据库，如何优化呢 
                    
                       

                             求一条SQL语句：实现把表中整型或者浮点型的数据求和解决思路