二：Recovery models（恢复模式）

For each database that you create in SQL Server, with the exception of the system databases, you can configure it to use one of three possible recovery models (simple, full, bulk-logged).  Here is a simple script to display the recovery models of all on-line databases:

在数据库中创建的每个数据库，除了系统数据之外，你都能配置三种恢复模式（简单、完全、大量日志）中的一个。如下是显示在线数据库的恢复模式：

SELECT name, (SELECT DATABASEPROPERTYEX(name, 'RECOVERY')) RecoveryModel FROM master..sysdatabases ORDER BY name

If you are running SQL Server 2005 or later, you can use this script instead:

如果你运行在SQL Server 2005或者更高的版本上，你可以使用如下脚本代替：

SELECT name, recovery_model_desc FROM master.sys.databases ORDER BY name

This is how you can change a database's recovery model to the simple recovery model:

如下是教你改变数据库的恢复模式成简单恢复模式：

ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE

 to the full recovery model:

改成完全恢复模式：

ALTER DATABASE AdventureWorks SET RECOVERY FULL

and to the bulk-logged recovery model:

改成大日志恢复模式：

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED

Which recovery model do you need to use?  It depends on how much data you can afford to lose.  Let's use the following diagram to illustrate the difference between the recovery models, where a full database backup is performed at 9:00 a.m, and 11 a.m.

你要使用哪种恢复模式？那是取决于你能承担的起丢失多少数据。让我们用下面的图来解释恢复模式之前的区别，下面这张图是一个数据库在9：00am 和11：00am完成的完全备份。

 

The simple recovery model（简单恢复模式）

 Assume that there was a hardware failure at 10:45  a.m.  If the database was using the simple recovery model, you would have lost 105 minutes of work.  The latest point at which you can recover the database to is 9:00 a.m, since that was the time the last full backup that was made.  You could schedule differential backups to run periodically e.g.

 假设硬件在 10：45am坏了。如果数据库采用的是简单恢复模式，你将丢失105分钟的数据。你最近的恢复时间点是你在9：00am做的完全备份。你可以规划差异备份定期进行。例如：

 

In this case, you would lose 45 minutes of work.  Now, assuming that a user deleted a critical table at 9:50 a.m.  Can you recover to the point in time just before the deletion?  No.  The differential backup contains only the changed data pages.  It cannot be used to restore to a specific point in time.  You'll have to restore the database to its 9 a.m state, and redo 49 minutes of work.  Then, you'll also need to redo the work that was performed after the deletion up to the time the error was discovered.

在这种情况下，你将丢失45分钟的数据。现在，我们假设用户在9：50删除了主要的表。你能恢复到删除的那个时间点前数据吗？不能。差异备份只包含了数据的变化页，他不能用于去恢复特定时间点的数据。你将不得不去将数据库恢复到9:00am,然后重做49分钟的工作。此刻，你也必须去完成删除数据时间之后到发现错误之间的工作。

The full recovery model（完全恢复模式）

If no transaction log backups are made between 9 a.m and 11 a.m, you would face the same situation as you would if the database had been using the simple recovery model. In addition, your transaction log file would be much larger, as SQL Server will not truncate committed and checkpointed transactions until they have been backed up.

如果你没做事物日志备份在 9:00至11：00之前，你将面对的同样的情况就和你使用的简单恢复模式一样。另外，你的事务日志会非常大，因为SQL Server不会删除已经提交和已经CheckPoint的事务，直到它们被备份。

转载于:https://www.cnblogs.com/huaan011/p/4290435.html