SQL Server 数据库备份和还原认识和总结 （一）
(编辑：jimmy 日期: 2024/11/25 浏览：3 次 )
可能许多同学对SQL Server的备份和还原有一些了解，也可能经常使用备份和还原功能，我相信除DBA之外我们大部分开发员队伍对备份和还原只使用最基础的功能，对它也只有一个大概的认识，如果对它有更深入的认识，了解它更全面的功能岂不是更好，到用时会得心应手。因为经常有中小型客户公司管理人员对数据库不了解或掌握不牢，会请我们技术人员出马找回丢失的数据或硬件损坏移动数据的现象，或其它情况的发生。
首先从数据库【恢复模式】说起，因为数据库如果恢复模式设置不正确，会导致数据无法还原。
SQL Server 2012对数据库备份和还原与SQL Server 2008基本没有太大变化，仅对还原时间点选择UI和相关还原选项做了小改动，备份和还原核心没有做变化，这只是个人的掌握和了解，仅供参考，具体还请以官方文档为依据。以下利用SQL Server 2012 SSMS(SQL Server Management Studio)做图例演示。
SQL Server 2008数据库恢复模式分为三种：完整恢复模式、大容量日志恢复模式、简单恢复模式。
◆ 大容量日志恢复模式。它是对完整恢复模式的补充。简单地说就是要对大容量操作进行最小日志记录，节省日志文件的空间（如导入数据、批量更新、SELECT INTO等操作时）。比如一次在数据库中插入数十万条记录时，在完整恢复模式下每一个插入记录的动作都会记录在日志中，使日志文件变得非常大，在大容量日志恢复模式下，只记录必要的操作，不记录所有日志，这样一来，可以大大提高数据库的性能，但是由于日志不完整，一旦出现问题，数据将可能无法恢复。因此，一般只有在需要进行大量数据操作时才将恢复模式改为大容量日志恢复模式，数据处理完毕之后，马上将恢复模式改回完整恢复模式。
◆ 简单恢复模式。在该模式下，数据库会自动把不活动的日志删除，因此简化了备份的还原，但因为没有事务日志备份，所以不能恢复到失败的时间点。通常，此模式只用于对数据库数据安全要求不太高的数据库。并且在该模式下，数据库只能做完整和差异备份。
了解了以上数据库还原模式后，一般就应该知道将自己的数据库设置为何种模式了。备份数据库，SQL Server 2008提供了四种备份方式：完整备份、差异备份、事务日志备份、文件和文件组备份。
◆ 差异备份：差异备份是完整备份的补充，只备份上次完整备份后更改的数据。相对于完整备份分来说，差异备份的数据量比完整数据备份小，备份的速度也比完整备份要快。因此，差异备份通常作为常用的备份方式。在还原数据时，要先还原前一次做的完整备份，然后还原最后一次所做的差异备份，这样才能让数据库里的数据恢复到与最后一次差异备份时的内容相同。
◆ 事务日志备份：事务日志备份只备份事务日志里的内容。事务日志记录了上一次完整备份或事务日志备份后数据库的所有变动过程。事务日志记录的是某一段时间内的数据库变动情况，因此在进行事务日志备份之前，必须要进行完整备份。与差异备份类似，事务日志备份生成的文件较小、占用时间较短，但是在还原数据时，除了先要还原完整备份之外，还要依次还原每个事务日志备份，而不是只还原最后一个事务日志备份（这是与差异备份的区别）。
◆ 文件和文件组备份。如果在创建数据库时，为数据库创建了多个数据库文件或文件组，可以使用该备份方式。使用文件和文件组备份方式可以只备份数据库中的某些文件，该备份方式在数据库文件非常庞大时十分有效，由于每次只备份一个或几个文件或文件组，可以分多次来备份数据库，避免大型数据库备份的时间过长。另外，由于文件和文件组备份只备份其中一个或多个数据文件，当数据库里的某个或某些文件损坏时，可能只还原损坏的文件或文件组备份。
完整备份可能比较好理解。例如，在2012年1月1日早上8点进行了完整备份，那么将来在还原时，就可以恢复到2012年1月有1日早上8点时的数据库状态。
差异备份是备份完整备份后的数据变动情况。例如，在2012年1月1日早上8点进行了完整备份后，在1月2日和1月3日又分别进行了差异备份，那么在1月2日的差异备份里记录的是从1月1日到1月2日这一段时间里的数据变动情况，而在1月3日的差异备份里记录的是从1月1日到1月3日这一段时间里的数据变动情况。因此，如果要还原到1月3日的状态，只要先还原1月1日做的完整备份，再还原1月3日做的差异备份就可以了。
事务日志备份是以事务日志文件作为备份对象，相当于将数据库里的每一个操作都记录下来了。假设在2012年1月1日早上8点进行了完整备份后，到1月2日早上8点为止，数据库里的数据变动了100次，如果此时做了差异备份，那么差异备份记录的是第100次数据变动后的数据库状态，而如果此时做了事务日志备份，备份的将是这100次的数据变动情况。
再举一个例子，例如在2012年1月1日早上8点进行了完整备份后，在1月2日和1月3日又进行了事务日志备份，那么在1月2日的事务日志备份里记录的是从1月1日到1月2日这一段时间里的数据变动情况，而在1月3日的事务日志备份里记录的是从1月2日到1月3日这一段时间里的数据变动情况。因此，如果要还原到1月3日的数据，需要先还原1月1日做的完整备份，再还原1月2日做的事务日志备份，最后还要还原1月3日所做的事务日志备份。
了解了以上数据库备份方式后，便可以针对自己的数据库利用以上方式来备份数据库了。合理备份数据库需要考虑几方面，首先是数据安全，其次是备份文件大小，最后是做备份和还原能承受的时间范围。
例如，如果数据库里每天变动的数据量很小，可以每周（周日）做一次完整备份，以后的每天（下班前）做一次事务日志备份，那么一旦数据库发生问题，可以将数据恢复到前一天（下班时）的状态。
当然，也可以在周日时做一次完整备份，周一到周六每天下班前做一次差异备份，这样一旦数据库发生问题，同样可以将数据恢复到前一天下班时的状态。只是一周的后几天做差异备份时，备份的时间和备份的文件都会跟着增加。但这也有一个好处，在数据损坏时，只要恢复完整备份的数据和前一天差异备份的数据即可，不需要去恢复每一天的事务日志备份，恢复的时间会比较短。
如果数据库里的数据变动得比较频繁，损失一个小时的数据都是十分严重的损失时，用上面的办法备份数据就不可行了，此时可以交替使用三种备份方式来备份数据库。
例如，每天下班时做一次完整备份，在两次完整备份之间每隔八小时做一次差异备份，在两次差异备份之间每隔一小时做一次事务日志备份。如此一来，一旦数据损坏可以将数据恢复到最近一个小时以内的状态，同时又能减少数据库备份数据的时间和备份数据文件的大小。
在前面还提到过当数据库文件过大不易备份时，可以分别备份数据库文件或文件组，将一个数据库分多次备份。在现实操作中，还有一种情况可以使用到数据库文件的备份。例如在一个数据库中，某些表里的数据变动得很少，而某些表里的数据却经常改变，那么可以考虑将这些数据表分别存储在不同的文件或文件组里，然后通过不同的备份频率来备份这些文件和文件组。但使用文件和文件组来进行备份，还原数据时也要分多次才能将整个数据库还原完毕，所以除非数据库文件大到备份困难，否则不要使用该备份方式。
针对以上备份方案，能看出数据还是不完整吗？比如昨天夜间12点做了完整备份，每隔一小时做了一次事务日志备份，最后一次事务日志备份是今天中午12点，现在是今天中午12点10分，发现数据库数据遭到丢失或破坏，可最后一次事务日志备份是今天中午12点，如果我此时将数据库恢复到12点，那么12点后至12点10分前没遭到破坏的操作数据将丢失（比如数据库有三个表，一个表的数据遭到破坏，其它两个表的数据被其它用户变动）。此时就要用到【尾部日志备份】，尾部日志备份原理是从最后一次事务日志备份的时间点开始，将之后的所有操作进行备份，还原时便可以找到12点后操作的正确数据了。
注：进行尾部日志备份时，数据库将强制停止数据库，此时如果不停止数据库，还有用户继续操作，尾部日志备份将失去意义。SQL Server 2012如果你最后一次备份事务日志后，对数据进行过改动，即发生过事务日志（也就是当前日志文件记录的LSN(日志序列号)大于最后一次事务日志备份里记录的最大LSN，SQL Server通过LSN来区分日志的记录），并尚未对尾部日志备份，它会提示并要求你必须先做尾部备份。
实例部分：
◆ 首先进行完整备份【MyTest.bak】，然后再基于此备份文件进行两次事务日志备份，最后一次事务日志备份时间为【2012-8-4 23:07】
图1-1：备份文件名为【MyTest.bak】，最后一次事务日志备份时间为【2012-8-4 23:07】
如果事务日志备份有两次，分别是“事务日志1”、“事务日志2”，那么在还原时去掉“事务日志1”复选框，“事务日志2”也会自动去掉，但只去掉“事务日志2”是允许的，这也充分验证了上文讲到的：“在还原数据时，除了先要还原完整备份之外，还要依次还原每个事务日志备份，而不是只还原最后一个事务日志备份”，不比差异备份可以基于完整备份，只还原最后一次差异备份即可。
图1-3：我【2012-08-04 23:36:53】插入一条数据，【2012-08-04 23:37:44】又做了一次事务日志备份3，然后再将时间点还原到【2012-08-04 23:36:00】，还原后，此时间点后的数据就没有了。
【注意：在还原时会提示数据库正在使用，一般要断开全部连接后，才能进行还原。我一般使用脚本将数据脱机再联机，所有连接将会全部断开】
alter database MyTest set offline with ROLLBACK IMMEDIATE--脱机
alter database MyTest set online with ROLLBACK IMMEDIATE--联机
图2-1：备份文件名为【MyTestA.bak】，备份时间为【2012-8-4 22:33】
首先从数据库【恢复模式】说起，因为数据库如果恢复模式设置不正确，会导致数据无法还原。
SQL Server 2012对数据库备份和还原与SQL Server 2008基本没有太大变化，仅对还原时间点选择UI和相关还原选项做了小改动，备份和还原核心没有做变化，这只是个人的掌握和了解，仅供参考，具体还请以官方文档为依据。以下利用SQL Server 2012 SSMS(SQL Server Management Studio)做图例演示。
SQL Server 2008数据库恢复模式分为三种：完整恢复模式、大容量日志恢复模式、简单恢复模式。
◆ 大容量日志恢复模式。它是对完整恢复模式的补充。简单地说就是要对大容量操作进行最小日志记录，节省日志文件的空间（如导入数据、批量更新、SELECT INTO等操作时）。比如一次在数据库中插入数十万条记录时，在完整恢复模式下每一个插入记录的动作都会记录在日志中，使日志文件变得非常大，在大容量日志恢复模式下，只记录必要的操作，不记录所有日志，这样一来，可以大大提高数据库的性能，但是由于日志不完整，一旦出现问题，数据将可能无法恢复。因此，一般只有在需要进行大量数据操作时才将恢复模式改为大容量日志恢复模式，数据处理完毕之后，马上将恢复模式改回完整恢复模式。
◆ 简单恢复模式。在该模式下，数据库会自动把不活动的日志删除，因此简化了备份的还原，但因为没有事务日志备份，所以不能恢复到失败的时间点。通常，此模式只用于对数据库数据安全要求不太高的数据库。并且在该模式下，数据库只能做完整和差异备份。
了解了以上数据库还原模式后，一般就应该知道将自己的数据库设置为何种模式了。备份数据库，SQL Server 2008提供了四种备份方式：完整备份、差异备份、事务日志备份、文件和文件组备份。
◆ 差异备份：差异备份是完整备份的补充，只备份上次完整备份后更改的数据。相对于完整备份分来说，差异备份的数据量比完整数据备份小，备份的速度也比完整备份要快。因此，差异备份通常作为常用的备份方式。在还原数据时，要先还原前一次做的完整备份，然后还原最后一次所做的差异备份，这样才能让数据库里的数据恢复到与最后一次差异备份时的内容相同。
◆ 事务日志备份：事务日志备份只备份事务日志里的内容。事务日志记录了上一次完整备份或事务日志备份后数据库的所有变动过程。事务日志记录的是某一段时间内的数据库变动情况，因此在进行事务日志备份之前，必须要进行完整备份。与差异备份类似，事务日志备份生成的文件较小、占用时间较短，但是在还原数据时，除了先要还原完整备份之外，还要依次还原每个事务日志备份，而不是只还原最后一个事务日志备份（这是与差异备份的区别）。
◆ 文件和文件组备份。如果在创建数据库时，为数据库创建了多个数据库文件或文件组，可以使用该备份方式。使用文件和文件组备份方式可以只备份数据库中的某些文件，该备份方式在数据库文件非常庞大时十分有效，由于每次只备份一个或几个文件或文件组，可以分多次来备份数据库，避免大型数据库备份的时间过长。另外，由于文件和文件组备份只备份其中一个或多个数据文件，当数据库里的某个或某些文件损坏时，可能只还原损坏的文件或文件组备份。
完整备份可能比较好理解。例如，在2012年1月1日早上8点进行了完整备份，那么将来在还原时，就可以恢复到2012年1月有1日早上8点时的数据库状态。
差异备份是备份完整备份后的数据变动情况。例如，在2012年1月1日早上8点进行了完整备份后，在1月2日和1月3日又分别进行了差异备份，那么在1月2日的差异备份里记录的是从1月1日到1月2日这一段时间里的数据变动情况，而在1月3日的差异备份里记录的是从1月1日到1月3日这一段时间里的数据变动情况。因此，如果要还原到1月3日的状态，只要先还原1月1日做的完整备份，再还原1月3日做的差异备份就可以了。
事务日志备份是以事务日志文件作为备份对象，相当于将数据库里的每一个操作都记录下来了。假设在2012年1月1日早上8点进行了完整备份后，到1月2日早上8点为止，数据库里的数据变动了100次，如果此时做了差异备份，那么差异备份记录的是第100次数据变动后的数据库状态，而如果此时做了事务日志备份，备份的将是这100次的数据变动情况。
再举一个例子，例如在2012年1月1日早上8点进行了完整备份后，在1月2日和1月3日又进行了事务日志备份，那么在1月2日的事务日志备份里记录的是从1月1日到1月2日这一段时间里的数据变动情况，而在1月3日的事务日志备份里记录的是从1月2日到1月3日这一段时间里的数据变动情况。因此，如果要还原到1月3日的数据，需要先还原1月1日做的完整备份，再还原1月2日做的事务日志备份，最后还要还原1月3日所做的事务日志备份。
了解了以上数据库备份方式后，便可以针对自己的数据库利用以上方式来备份数据库了。合理备份数据库需要考虑几方面，首先是数据安全，其次是备份文件大小，最后是做备份和还原能承受的时间范围。
例如，如果数据库里每天变动的数据量很小，可以每周（周日）做一次完整备份，以后的每天（下班前）做一次事务日志备份，那么一旦数据库发生问题，可以将数据恢复到前一天（下班时）的状态。
当然，也可以在周日时做一次完整备份，周一到周六每天下班前做一次差异备份，这样一旦数据库发生问题，同样可以将数据恢复到前一天下班时的状态。只是一周的后几天做差异备份时，备份的时间和备份的文件都会跟着增加。但这也有一个好处，在数据损坏时，只要恢复完整备份的数据和前一天差异备份的数据即可，不需要去恢复每一天的事务日志备份，恢复的时间会比较短。
如果数据库里的数据变动得比较频繁，损失一个小时的数据都是十分严重的损失时，用上面的办法备份数据就不可行了，此时可以交替使用三种备份方式来备份数据库。
例如，每天下班时做一次完整备份，在两次完整备份之间每隔八小时做一次差异备份，在两次差异备份之间每隔一小时做一次事务日志备份。如此一来，一旦数据损坏可以将数据恢复到最近一个小时以内的状态，同时又能减少数据库备份数据的时间和备份数据文件的大小。
在前面还提到过当数据库文件过大不易备份时，可以分别备份数据库文件或文件组，将一个数据库分多次备份。在现实操作中，还有一种情况可以使用到数据库文件的备份。例如在一个数据库中，某些表里的数据变动得很少，而某些表里的数据却经常改变，那么可以考虑将这些数据表分别存储在不同的文件或文件组里，然后通过不同的备份频率来备份这些文件和文件组。但使用文件和文件组来进行备份，还原数据时也要分多次才能将整个数据库还原完毕，所以除非数据库文件大到备份困难，否则不要使用该备份方式。
针对以上备份方案，能看出数据还是不完整吗？比如昨天夜间12点做了完整备份，每隔一小时做了一次事务日志备份，最后一次事务日志备份是今天中午12点，现在是今天中午12点10分，发现数据库数据遭到丢失或破坏，可最后一次事务日志备份是今天中午12点，如果我此时将数据库恢复到12点，那么12点后至12点10分前没遭到破坏的操作数据将丢失（比如数据库有三个表，一个表的数据遭到破坏，其它两个表的数据被其它用户变动）。此时就要用到【尾部日志备份】，尾部日志备份原理是从最后一次事务日志备份的时间点开始，将之后的所有操作进行备份，还原时便可以找到12点后操作的正确数据了。
注：进行尾部日志备份时，数据库将强制停止数据库，此时如果不停止数据库，还有用户继续操作，尾部日志备份将失去意义。SQL Server 2012如果你最后一次备份事务日志后，对数据进行过改动，即发生过事务日志（也就是当前日志文件记录的LSN(日志序列号)大于最后一次事务日志备份里记录的最大LSN，SQL Server通过LSN来区分日志的记录），并尚未对尾部日志备份，它会提示并要求你必须先做尾部备份。
实例部分：
◆ 首先进行完整备份【MyTest.bak】，然后再基于此备份文件进行两次事务日志备份，最后一次事务日志备份时间为【2012-8-4 23:07】
图1-1：备份文件名为【MyTest.bak】，最后一次事务日志备份时间为【2012-8-4 23:07】
图1-2：选择备份文件“MyTest.bak”后，此时能看到“要还原的备份集”列表里显示有备份文件，分别列出了完整备份文件和两次事务日志备份。
如果事务日志备份有两次，分别是“事务日志1”、“事务日志2”，那么在还原时去掉“事务日志1”复选框，“事务日志2”也会自动去掉，但只去掉“事务日志2”是允许的，这也充分验证了上文讲到的：“在还原数据时，除了先要还原完整备份之外，还要依次还原每个事务日志备份，而不是只还原最后一个事务日志备份”，不比差异备份可以基于完整备份，只还原最后一次差异备份即可。
图1-3：我【2012-08-04 23:36:53】插入一条数据，【2012-08-04 23:37:44】又做了一次事务日志备份3，然后再将时间点还原到【2012-08-04 23:36:00】，还原后，此时间点后的数据就没有了。
【注意：在还原时会提示数据库正在使用，一般要断开全部连接后，才能进行还原。我一般使用脚本将数据脱机再联机，所有连接将会全部断开】
alter database MyTest set offline with ROLLBACK IMMEDIATE--脱机
alter database MyTest set online with ROLLBACK IMMEDIATE--联机
图2-1：备份文件名为【MyTestA.bak】，备份时间为【2012-8-4 22:33】
图2-2：先选择最后一次完整备份文件MyTestA，此时能看到“要还原的备份集”列表里显示有备份文件，然后再点“时间线”按钮进入“备份时间线”界面，将最后一次完整备份时间由“22:33:41”改为它之前任何一个时间点，如改为“22:32:41”，然后再确定，会看到图2-3的效果。
图2-3：只要基于完整备份再还原到某个时间点是不允许的，因为完整备份后没做过任何差异备份或事务日志备份。此时能看到“要还原的备份集”列表里显示为空，无法进行还原。
下一篇：编程经验点滴 动态SQL的拼接技巧