BackUp of SQL Server

大家好，很长时间没写东西了，最近在浩方干星际，真是输的连妈都不认，算了，还是回来搞数据库有前途一点，起码在这里我还能勉强找到那么点自信！！！
做备份就像买保险一样，你可以不做，每天提心吊胆的过日子，你也可以做，让系统运行的通常无阻，有顽强的再生能力，同时大家也会忽略你的存在，总是要出下乱子，然后你再来扮演下救世主，这样大家才会知道你的存在是有价值的，你的工资应该是×××的，所以个人感觉做备份是件不大讨好的事！！！
MICROSOFT SQL SERVER的备份机制比SYBASE的真是强大的好多，首先sybase不支持数据库的部分恢复，SQL SERVER的文件与文件组备份很好的实现了这一点，sybase对于错误删除记录的操作显得有点力不从心，因为load tran不支持指定时间点或命名事务，sybase也不存在恢复模型，这样对于不同企业间恢复机制的确定没有一个直观的尺度.我会在下面的文字中逐个说明SQL SERVER的这些优点(也许是下篇文章)

恢复模型：
SQL SERVER有3个恢复模型，简单恢复，完全恢复和大容量日志恢复，每个模型各有自己的特点，也适用于不同的企业备份需求，模型之间可以相互切换，这也是个比较容易出问题的地方

简单恢复：
就像其名字一样，这种恢复模型是最简单的，他只支持数据库完整备份和差异备份，另外对于节省日志文件空间也有很好的支持，因为在检查点处会自动截断日志，所以日志文件几乎不会增长，但提交较大的事务时情况令当别论，由于备份能力有限，其风险指数也是很高的，如果数据库损坏，只能恢复到上次完整备份或最后一次差异备份，对于后面的事务处理就无能为力了,准确一点应该是非活动的事务日志都被删除了
   
大容量日志恢复：
这个模型很像后面将提到的完全恢复，但还是有很多不同，他支持数据库完整，差异和日志备份，对于诸如SELECT INTO，BULK INSERT,BCP等操作大容量的日志操作提供了性能上的优化，在完全恢复模式下，日志会记录上述操作的具体细节，而本模式中只会在日志中记载有这么一回事，具体细节忽略不计，这样存储速度就有很大提高，也节约了日志空间，问题也就出现了，由于日志记录中包含有忽略细节的最小日志更改，所以大容量恢复模型不支持具体时间点的恢复，也就是说不能在restore log中指定stopat子句，下面是操作和出错信息

/*大容量日志恢复模型
restore database chj733 from disk = @#e:\chj733_dat.bck@# with norecovery
go
restore log chj733 from disk = @#e:\chj733_log.bck@# with file = 1,stopat = @#2004-11-09 15:06:04.810@#,recovery
go
已处理 2016 页，这些页属于数据库 @#chj733@# 的文件 @#chj733@#（位于文件 1 上）。
已处理 1 页，这些页属于数据库 @#chj733@# 的文件 @#chj733_log@#（位于文件 1 上）。
RESTORE DATABASE 操作成功地处理了 2017 页，花费了 3.687 秒（4.479 MB/秒）。
服务器: 消息 4327，级别 16，状态 1，行 1
此备份集中的日志包含最小日志记录更改。禁止进行时点恢复。RESTORE 将前滚到日志的结尾，而不恢复数据库。
已处理 7152 页，这些页属于数据库 @#chj733@# 的文件 @#chj733@#（位于文件 1 上）。
已处理 1736 页，这些页属于数据库 @#chj733@# 的文件 @#chj733_log@#（位于文件 1 上）。
RESTORE LOG 操作成功地处理了 8888 页，花费了 6.668 秒（10.919 MB/秒）。
*/
同时在大容量日志模式下备份事务日志需要访问数据文件，而很多灾难发生后基本上数据文件就没法访问了，所以采用这种模式时你的防着这一点，很有可能你会丢失自上次事务备份以后所有的数据,一般可以在准备进行大量数据装载或建立比较庞大的索引时将模型切换到大容量日志方式，操作完成后在切换回完全模式。

完全恢复：
这种模式风险是最小的，联机帮助上宣称这种模式可以将数据库恢复到任何即时点，理论上可以将数据丢失的可能性减至0（事实上并非如此），上述两个模型中打NO的在这里基本上可以打YES，他支持所有的备份和恢复方式，支持指定即时点，日志对BCP等大数据装载操作有详细记载，等等，这些特性也引发了一些问题，日志文件开销较大，需要频繁转存日志

恢复模型就讲到这，大家可以试着用BCP加载一些数据看看，在各个模式下观察下日志文件的大小变化是不是符合上面的说法，做之前最好先压缩下日志文件，如果日志文件里面有足够的空间，你加载的数据份量又不够的话，你会看不到文件的变化的，省得到时候又来骂我在这里胡说八道！！！

备份类型：
先抛开文件备份，SQL SERVER有3种备份类型，完整备份，差异备份，日志备份，你必须先明白每种备份从哪里开始，从哪里结束，他里面的内容是什么

完整备份是数据库的一个完整拷贝，包括事务日志，数据库中的所有对象，等等

差异备份从上次完整备份之后开始，创建所有修改页的拷贝，注意，他是从上次完整备份后开始的，所以应用差异备份时只需最后的一次备份，而不是逐个应用所有的差异备份，当然你有这个闲功夫，也可以这么干

日志备份是备份上次日志备份后所有的事务记录，记住，不是上次完整备份。在应用事务日志备份时将前滚所有的事务记录，并且这些记录应该是连续的，另外也会回滚没有COMMIT的日志记录
   
这些老掉牙的话应该好好的理解，最起码在面试的时候应该很流利的背出来，下面看一个比较常见的错误。

问题1：
/*
服务器: 消息 4305，级别 16，状态 1，行 2
此备份集中的日志开始于 LSN 641000000005900001，该 LSN 太晚，无法应用到数据 库。包含 LSN 641000000005600001 的较早的日志备份可以还原。
服务器: 消息 3013，级别 16，状态 1，行 2
RESTORE LOG 操作异常终止。
*/
相信这个信息只要是做过备份的人都知道，在应用完整备份＋日志备份恢复数据库时提示只能应用数据库备份，而日志备份由于LSN太早或太晚无法应用，这是怎么回事阿？？？LSN表示事务日志记录的唯一序号，SQL SERVER会记录对数据库的每次操作，这些操作总有个先来后到的，LSN就是系统发给他们的顺序号，日志备份恢复时要求所有的备份集叠加时能生成一个连续的LSN链，这个就是问题所在(如果对上述日志概念不了解的话可以阅读精华区SQL日志概念这一篇,或是看BOL)。查看下备份文件就可以知道答案
restore headeronly from disk = @#e:\chj733_dat.bck@#  ――数据库备份
restore headeronly from disk = @#e:\chj733_log.bck@#  ――日志备份
结果（我只列出了比较有用的几项）
―――――――――――――――――――――――――――――――――――――
Position              FirstLsn                   LastLsn
  1           641000000005400001        641000000005600001   ――数据备份
  1           641000000005900001        641000000006100001   ――日志备份
―――――――――――――――――――――――――――――――――――――
position表示这个设备中备份集的位置，不是可以在一个设备里多次备份的吗？每备份一次就生成一个备份集，按先后顺序一直排列下来，Position就可以定位你想应用那个备份集，对应于restore中的with file的值，这里我只备份了一次，所以就只有一个集
FirstLsn和LastLsn:分别标识这个备份集中的起始事务链号和终止事务链号
当你运用这两个备份还原数据库时，系统会读取备份集的头信息，判断这些链号是不是连续的，很显然数据备份最后的是641000000005600001，日志开头的是641000000005900001，中间差了一截，所以从这个以后的所有日志备份都不能运用了，就像火车车厢一样，前面断了，你后面连得再好也跑不起来，你会发现有时候日志的FirstLsn会小于数据的LastLsn,这个也征实了日志备份是从上次日志备份结尾处开始的说法，但日志备份的LastLsn不能小于数据备份的LastLsn
一般容易出现像这种日志脱节的操作是切换恢复模型，从简单切换到完全恢复，很多新手都是这样，数据库建好了用了几天，做个完整备份，然后在做日志备份，结果报错说简单模型不能做日志备份，于是切换到完全模型继续日志备份，这样日志链就脱节了，解决方法是备份后用restore headeronly查看下日志链是否完整，不完整的话需要重做完整备份或差异备份，再继续日志备份，如果到数据库出现故障时再检查，那你就准备卷铺盖走人吧

这应该比较细的东西了，没有真正做过又怎会知道里面的来龙去脉呢？记得公司有个同事曾对我说：“备份很简单阿，就是几条load语句嘛，记住就搞定啦”我从不说备份很难，但我敢肯定，叫他来做备份，不哭爹喊娘才怪！！！另外如果你只是在像pubs这种只有50M的数据库上非常流畅的运行了上面所说的各种语句的话，你就可以对大家说你会写备份语句，但不要说你会备份，遇到一个900多G的数据库，情况就大不一样了，就像打星际一样，掌握如何造兵，是怎么个顺序，每个兵的攻击范围很容易，但如何在短时间既要侦察敌情，防止敌人骚扰，又要造出数量可观的兵种，灵活搭配应付各种各样的战争场面就难了

最后的话题:
也许有人对恢复语句后面的with file子句不大理解,或是对于备份集,家族成员,媒体集这些概念混淆不清,其实这些概念不需要记住,你只要知道备份是以什么个形式存在就可以了,当备份到一个设备上时,可以追加备份,也可以覆盖以前的备份,每追加一次,这个设备上的文件就多了一个,这个文件就是说说的备份集,with file = 2就标识你要应用这个设备上的第二个备份集来还原数据库,查看备份集信息可以使用restore headeronly from device_name命令,有时候你可能为了加快备份执行速度,将数据库备份到多个设备上(比如磁带机或不同的物理磁盘),利用并行写入来提高IO效率,如:
Backup database chj733 to disk = ‘c:\data_bck1.bck’,disk = ‘i:\data_bck2.bck’
这样两个设备在一起就组成了一个媒体集,每个设备称为集内的家族成员,他们就是一个整体了,每次恢复和转存时必须使用所有家族成员,下面的出错信息大家应该明白是怎么回事
/*加载数据库
Restore database chj733 from disk = ‘c:\data_back1.bck’
服务器: 消息 3132，级别 16，状态 1，行 1
数据库 ‘chj733 ‘ 的媒体集有 2 个家族成员，但只提供了 1 个。必须提供所有的成员。
服务器: 消息 3013，级别 16，状态 1，行 1
RESTORE DATABASE 操作异常终止。
*/
/*转存数据库
Backup database chj733 to disk = ‘c:\data_back1.bck’
服务器: 消息 3132，级别 16，状态 1，行 1
数据库 @#chj733@# 的媒体集有 2 个家族成员，但只提供了 1 个。必须提供所有的成员。
服务器: 消息 3013，级别 16，状态 1，行 1
BACKUP DATABASE 操作异常终止。
*/
查看媒体集信息可使用restore labelonly from device_name命令,如果你选择用多个家族成员的媒体集来备份数据库,你就得保证每个成员的安全,任何一个成员的丢失都将导致整个备份的失败,如果你想让某个设备从媒体集退出,再重新做别的备份,基本上只有利用format重写所有的媒体头,具体实现可以参考BOL上backup语句的with format,init,skip子句,注意他们之间的相互搭配

好了，喋喋不休了半天，相信大家都烦了，闪！！！

原文转自：http://www.ltesting.net