sqlserver备份恢复__学习笔记1

 

 


 


前记:


 今年4月份的时候，做过一个sqlserver2000到sqlserver2005的数据迁移，而且sqlserver2005还是建立在windows2003 mscs集群上的双active故障转移集群，弄了好几天才搭建好环境，自以为对sqlserver很熟悉了，但实际当时对sqlserver的原理研究的很少，后来有一个维护项目，是sqlserver2000,客户说他们的事务日志撑爆了硬盘，问我该怎么处理，我于是认真研究了一下sqlserver的日志机制，备份恢复原理，发现它和Oracle有相通之处，也有不同的地方，以下是一些知识的收集和总结，以备不时之需吧，毕竟工作中搞sqlserver的机会比较少了...


 


1.交易日志(transaction logs)


 


   交易日志是针对数据库改变所做的记录，它可以记录针对数据库的任何操作，并将记录结果保存在独立的文件中。对于任何每一个交易过程，交易日志都有非常全面的记录，根据这些记录可以将数据文件恢复成交易前的状态。从交易动作开始，交易日志就处于记录状态，交易过程中对数据库的任何操作都在记录范围，直到用户点击提交或后退后才结束记录。每个数据库都拥有至少一个交易日志以及一个数据文件。



　　出于性能上的考虑，SQL Server将用户的改动存入缓存中，这些改变会立即写入交易日志，但不会立即写入数据文件。交易日志会通过一个标记点来确定某个交易是否已将缓存中的数据写入数据文件。当SQL Server重启后，它会查看日志中最新的标记点，并将这个标记点后面的交易记录抹去，因为这些交易记录并没有真正的将缓存中的数据写入数据文件。这可以防止那些中断的交易修改数据文件。


    （注解，类似于oracle的redo log file、redo log buffer、instance recover 机制）


 


2.日志的截除和压缩


   日志的截除，就是把日志中的旧记录标记为非活动状态，然后数据库会将新日志覆盖到旧日志的位置上，这样就可以防止交易日志的体积不断膨胀。


   截除可以发生在备份日志文件时，比如：backup log databasename with no_log


  


压缩的过程其实就是彻底删除旧日志，释放出空间。


 



3.恢复模型


 


3.1)简单恢复模型SIMPLE


   使用该模型可以将数据库恢复到上次备份的即时点，但无法还原到故障点。


   （注解，这个类似oracle的非归档模式）


3.2)完全恢复模型FULL


   完全恢复模型提供了最大的恢复灵活性。新数据库默认使用的就是这种恢复模型。利用这种模型，可以恢复数据库的一部分或者完全恢复。假设交易记录（transactions log）还没有被破坏，还可以在失败之前恢复出最后一次的已提交（committed）交易。在所有的恢复模型中，这种模型使用了最多的交易记录空间，并轻微影响了SQL Server的性能。
3.3)大容量日志记录恢复模型BULK_LOGGED


  


BULK_LOGGED恢复模型比FULL模型少了一些恢复选项，但是进行批操作（bulk operation）时它不会严重影响性能。在进行某些批操作时，由于它只需记录操作的结果，因此它使用了较少的记录空间。然而，用这种模型，你不能恢复数据库中的特定标记，也不能仅仅恢复数据库的一部分。


 


4.备份数据库


4.1)备份命令的例子


    backup database pubs to disk="c:/pubs.bak"


    BACKUP LOG pubs WITH NO_LOG;
    BACKUP LOG Northwind TO LogBackupDevice WITH NO_TRUNCATE


4.2)备份技巧


    每周一次备份主数据库。


    每天备份一次msdb数据库。


    用SQL Server Agent来安排你的备份工作的时间表。


   



5.当事务日志撑爆硬盘时的处理方法


5.1)方法一（在业务不繁忙时做） ：



 a)利用企业管理器完全备份数据库
 b)利用查询分析器，执行命令
   dump transaction 数据库名 with no_log
 c)再打开企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件--选择日志文件--在收 缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了。 
 d)再次完全备份数据库。




5.2)方法二（在业务不繁忙时做） ：


 使用工具：ProSqlLogClear.exe清除日志、使用前后都需要备份数据库


 


5.3)在清除完日志后，对于数据量不大且数据可以容忍一天的丢失的数据库，可以将恢复模型该为简单，然后每天做全备。步骤如下：


  a)右建数据库属性窗口--选项--故障还原模型--设为简单


  b)右键数据库属性窗口--选项--设置--选择"自动收缩"


  c)做一个维护计划，定期做每天的备份，并定制保留几天的备份