SQL Server 2005&2008备份与恢复 I,该如何处理
日期：2014-05-19 浏览次数：23568 次
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上，可以通过创建逻辑备份设备来完成备份。使用逻辑备份设备的好处是，当变更备份地址时，不需要更改备份脚本，只需要更改逻辑备份设备的定义即可。
创建逻辑备份设备的脚本如下：
删除备份设备的脚本：
上述脚本只是删除逻辑备份设备的定义，下述脚本将同时删除备份文件：
一篇旧小结，内容都来自互联网~
一、数据库存储概述
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
- 疑问 小弟我按一个字段排序查询出来的数据却是按其他字段排序
- TOPCount分组的有关问题
- SSRS自定义函数有关问题
- 关于数据库设计的3范式
- 请问一个关于sqlserver的连接有关问题
- 待各位答复，如何写目录名称是变量的bcp语句呢
- 求大神来个sql语句，文章置顶有关问题
- 怎么取中间的三条记录
- 小弟我想做一个测试，怎么向mssql快速插入10亿条数据
- 这个SQL把小弟我难住了，高手们进来看一下，统计和字符串处理的有关问题。
- 请教怎么获取SQLServer实例所占的硬盘空间大小
- 高手帮忙，SQL中导入ACCESS库，出现 异常源：MICRO JET DATABASE ENGINE
- SQL2005表格服务中ReportServerTempDB库的表能否手动清除数据
- SqlSever not in条件中怎么加入字符串（小弟新手，请大哥帮忙）
- 解密sqlserver2000存储过程的工具哪位高手有啊
- 请帮忙写个简单的SQL语句解决办法
- inner join和case能一起使用吗吗?解决方法
- 简单有关问题 关于时间计算
- 查询结果导出到excel有关问题，求教
- 有一个基础的有关问题
推荐阅读更多>